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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply