Hi All,
Looking to get advice on how to create a datetime field by combining int year and month fields with a '05' for the date. I'm trying this in my query for the field:
Cast(Convert(nvarchar,(ib.InvoiceAccountingYear + '-' + ib.InvoiceAccountingMonth + '-' + '05'))AS smalldatetime) AS FirstInvDt
but it's returning dates such as these, with an 01 as the date part:
2032-01-01 00:00:00
2030-01-01 00:00:00
2028-01-01 00:00:00
Any recommendations on how I can get the dates to appear with an '05'? Thanks in advance for your insight!
August 12, 2019 at 9:24 pm
Compare 😛
SELECT Cast(Convert(nvarchar,(2032 + '-' + 1 + '-' + '05'))AS smalldatetime)
vs
SELECT Cast(Convert(nvarchar,('2032' + '-' + '01' + '-' + '05'))AS smalldatetime)
August 12, 2019 at 9:37 pm
Use DATEFROMPARTS(Year, Month, Day).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
This is a problem with data type precedence. Numbers have a higher precedence than strings so your strings are being converted to numbers (specifically -0 or 0), so you're doing a sum instead of a concatenation. In other words, your conversion to nvarchar is in the wrong place.
Cast((CAST(ib.InvoiceAccountingYear AS NCHAR(4)) + N'-' + RIGHT(N'0' + CAST(ib.InvoiceAccountingMonth AS NVARCHAR(2)), 2) + N'-05'))AS smalldatetime) AS FirstInvDt
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 13, 2019 at 2:11 pm
Thank you, Phil Parkin!
August 13, 2019 at 2:13 pm
Thank you, drew.allen! That worked perfectly. I added in a Min, at the beginning, as I need to take the earliest date, and fiddled with the placement of the parentheses and it was good. I'm so grateful for this community and for experts like you all!
August 13, 2019 at 2:55 pm
Thanks for your input, ZZartin!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply