Convert int field into date and format issue

  • daniness

    SSCrazy

    Points: 2890

    Hi All,

    I'm hoping to get your advice on this. I'm trying to take an int field, AccountingPeriod, value, convert it to a date and then format it to populate a date field, but it's not getting the correct value for the month. Here's the query I'm using:

    ,LEFT(ppr.ReportDateYearMonth,4) + RIGHT(DateAdd(mm,-1,(CONVERT(date,(CAST(ppr.ReportDateYearMonth AS datetime))))),6) AS PeriodEndCalendar

    ,ppr.ReportDateYearMonth AS AccountingPeriod



    but it's not showing with the correct month value, which should be 1 month earlier than the one that is in the AccountingPeriod:

     
    Can someone please advise on why this isn't working? Thanks!

  • DesNorton

    SSC-Insane

    Points: 23056

    It appears that your ReportDateYearMonthg is a char(6) value.
    This should do the trick
    SELECT DATEADD(MONTH, -1, CONVERT(date, ppr.ReportDateYearMonth+'01', 112)) AS PeriodEndCalendar

  • daniness

    SSCrazy

    Points: 2890

    Hi @desnorton,

    Thanks for your suggestion, but when I try it, I'm getting an "Explicit conversion from data type int to date is not allowed" error...the ReportDateYearMonth field is actually type int. Any other suggestions? Thanks!

  • DesNorton

    SSC-Insane

    Points: 23056

    daniness - Monday, October 15, 2018 1:44 PM

    Hi @desnorton,

    Thanks for your suggestion, but when I try it, I'm getting an "Explicit conversion from data type int to date is not allowed" error...the ReportDateYearMonth field is actually type int. Any other suggestions? Thanks!

    OK, so you need to cast the int to a char ...
    SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

  • daniness

    SSCrazy

    Points: 2890

    Thanks, @desnorton....that actually fixed the issue! You're a genius! Could you please explain why that worked? I'm not clear on why we needed to cast to a char. Thanks again!

  • DesNorton

    SSC-Insane

    Points: 23056

    daniness - Monday, October 15, 2018 1:54 PM

    Thanks, @desnorton....that actually fixed the issue! You're a genius! Could you please explain why that worked? I'm not clear on why we needed to cast to a char. Thanks again!

    Your current YearMonth value is stored as an integer.  However, the integer number has no computed meaning in respect of the actual date.
    In order to cast it to a date, it needs to be broken down into YEAR + MONTH + DAY.  So by casting the 6 digit value a char, you get "YYYYMM".  We then add the '01' as a char(2) to get "YYYYMMDD", which is the ISO standard format for dates.  The CONVERT(date, "YYYYMMDD", 112) then casts the value to a valid date.

    Alternatively, we could multiply the YearMonth value by 100 and add 1 to get an integer that looks like "YYYYMMDD", and convert that to a char(8).  We can then use the CONVERT() to cast it to a valid date.
    SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(@ReportDateYearMonth *100 +1 AS char(8)), 112)) AS PeriodEndCalendar

  • DesNorton

    SSC-Insane

    Points: 23056

    Sorry, but that doesn't read right.  I seem to be having trouble articulating what my mind is thinking.

    Perhaps the CONVERT() documentation might help.

  • Jeffrey Williams

    SSC Guru

    Points: 88537

    Try this:

    Declare @acctPeriod int = 201801;
    Select dateadd(month, -1, datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1))

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams

    SSC Guru

    Points: 88537

    Jeffrey Williams 3188 - Monday, October 15, 2018 2:37 PM

    Try this:

    Declare @acctPeriod int = 201801;
    Select dateadd(month, -1, datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1))

    And if you really want the last day of the end of the period:

    Declare @acctPeriod int = 201801;
    Select eomonth(datefromparts(left(@acctPeriod, 4), right(@acctPeriod, 2), 1), -1)

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • daniness

    SSCrazy

    Points: 2890

    Thanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.

    SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

    I've tried the EOMONTH function, but it's not looking correct, as I checked and it's returning 30 for some of the months where there are 31 days:

    DATEADD(MONTH, -1, EOMONTH(CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112))) AS PeriodEndCalendar

    I'd appreciate any pointers on this. Thanks!

  • DesNorton

    SSC-Insane

    Points: 23056

    daniness - Monday, October 15, 2018 2:49 PM

    Thanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.

    SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

    I'd appreciate any pointers on this. Thanks!

    Instead of subtracting 1 month, subtract 1 day
    SELECT DATEADD(DAY, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

  • DesNorton

    SSC-Insane

    Points: 23056

    Here is a link to some Common Date Routines

  • daniness

    SSCrazy

    Points: 2890

    DesNorton - Monday, October 15, 2018 2:51 PM

    daniness - Monday, October 15, 2018 2:49 PM

    Thanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.

    SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

    I'd appreciate any pointers on this. Thanks!

    Instead of subtracting 1 month, subtract 1 day
    SELECT DATEADD(DAY, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

    Thanks @desnorton. That did it! You're truly brilliant!

  • drew.allen

    SSC Guru

    Points: 76735

    daniness - Monday, October 15, 2018 2:49 PM

    Thanks for your input as well, Jeffrey Williams. I do have a question...does anyone know how to get the last day of the month, instead of using the '01'? I'm wondering how this could be accomplished, due to some months having 28 or 29 days (depending on if it's a leap year or not), or 30, or 31 days.

    SELECT DATEADD(MONTH, -1, CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)) AS PeriodEndCalendar

    I've tried the EOMONTH function, but it's not looking correct, as I checked and it's returning 30 for some of the months where there are 31 days:

    DATEADD(MONTH, -1, EOMONTH(CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112))) AS PeriodEndCalendar

    I'd appreciate any pointers on this. Thanks!

    The EOMONTH function IS working correctly.  The problem is that you are processing your calculations in the wrong order.  You need to subtract one month BEFORE finding the EOMONTH rather than AFTER.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeff Moden

    SSC Guru

    Points: 996645

    daniness - Monday, October 15, 2018 1:20 PM

    Hi All,

    I'm hoping to get your advice on this. I'm trying to take an int field, AccountingPeriod, value, convert it to a date and then format it to populate a date field, but it's not getting the correct value for the month. Here's the query I'm using:

    ,LEFT(ppr.ReportDateYearMonth,4) + RIGHT(DateAdd(mm,-1,(CONVERT(date,(CAST(ppr.ReportDateYearMonth AS datetime))))),6) AS PeriodEndCalendar

    ,ppr.ReportDateYearMonth AS AccountingPeriod



    but it's not showing with the correct month value, which should be 1 month earlier than the one that is in the AccountingPeriod:

     
    Can someone please advise on why this isn't working? Thanks!

    For the life of me, I'll never understand why people use "PeriodEnd" instead of the much simpler in all ways "PeriodStart".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply