Customize a datetime field

• daniness

  SSCrazy

  Points: 2890

  August 12, 2019 at 9:13 pm

  Go to Answer

  #3670832

  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!

   

• ZZartin

  SSC-Dedicated

  Points: 30331

  August 12, 2019 at 9:24 pm

  #3670834

  Compare 😛

  SELECT Cast(Convert(nvarchar,(2032 + '-' + 1 + '-' + '05'))AS smalldatetime)

  vs

  SELECT Cast(Convert(nvarchar,('2032' + '-' + '01' + '-' + '05'))AS smalldatetime)

   

• Phil Parkin

  SSC Guru

  Points: 243531

  August 12, 2019 at 9:37 pm

  #3670845

  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.

• drew.allen

  SSC Guru

  Points: 76491

  August 12, 2019 at 9:43 pm

  Answer

  #3670846

  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

• daniness

  SSCrazy

  Points: 2890

  August 13, 2019 at 2:11 pm

  #3671028

  Thank you, Phil Parkin!

• daniness

  SSCrazy

  Points: 2890

  August 13, 2019 at 2:13 pm

  #3671029

  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!

• daniness

  SSCrazy

  Points: 2890

  August 13, 2019 at 2:55 pm

  #3671050

  Thanks for your input, ZZartin!