Customize a datetime field

  • daniness

    SSCrazy

    Points: 2890

    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: 30384

    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: 243840

    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: 76658

    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

    Thank you, Phil Parkin!

  • daniness

    SSCrazy

    Points: 2890

    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

    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