DATEADD YYYYMM

  • texpic

    SSCertifiable

    Points: 5882

    This gives me last month, can't figure out next month. 

    DECLARE @yrMo int = 201501

    select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)-1,112)

  • texpic

    SSCertifiable

    Points: 5882

    Geez, I'm stoopid, LOL  ..this works but  is there a better way

    DECLARE @yrMo int = 201501

    select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    texpic - Friday, August 10, 2018 7:45 PM

    Geez, I'm stoopid, LOL  ..this works but  is there a better way

    DECLARE @yrMo int = 201501

    select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)

    This will break 5 times for every year, in the months that have less than 31 days!
    😎

    Here is a better method, which does the calculation correctly and avoids the string conversion.

    DECLARE @YRMO INT = 201501;
    DECLARE @MSHIFT INT = 1;

    SELECT DATEADD(MONTH,((@YRMO % 100) -1) + @MSHIFT,DATEADD(YEAR,((@YRMO / 100) - 1900),0)) AS THE_DATE

  • Jeff Moden

    SSC Guru

    Points: 996619

    UGH!  This is what happens when people try to be "smart" about how to store dates and times except for maybe in a Calendar table.  This problem would be a cake walk if the data were stored correctly for processing rather than some poor attempt to save 2 or 4 bytes of space or saving the display value instead of the real value.


    --===== If this were in a table, you could add a constraint to accept only first-of-month dates.
    DECLARE @BetterYrMo DATE = '20150101' --OR DATETIME OR SMALLDATETIME OR even DATETIME2
    ;
     SELECT CONVERT(CHAR(6),DATEADD(mm,1,@BetterYrMo),112) --CONVERT only for display purposes here.
    ;
    --If they suddenly want a DASH between year and month, it's easy if the base data is temporal
    --instead of an INT.
     SELECT CONVERT(CHAR(7),DATEADD(mm,1,@BetterYrMo),121) --CONVERT only for display purposes here.
    ;

    --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)
    F

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Jeff Moden - Saturday, August 11, 2018 4:24 PM

    UGH!  This is what happens when people try to be "smart" about how to store dates and times except for maybe in a Calendar table.  This problem would be a cake walk if the data were stored correctly for processing rather than some poor attempt to save 2 or 4 bytes of space or saving the display value instead of the real value.


    --===== If this were in a table, you could add a constraint to accept only first-of-month dates.
    DECLARE @BetterYrMo DATE = '20150101' --OR DATETIME OR SMALLDATETIME OR even DATETIME2
    ;
     SELECT CONVERT(CHAR(6),DATEADD(mm,1,@BetterYrMo),112) --CONVERT only for display purposes here.
    ;
    --If they suddenly want a DASH between year and month, it's easy if the base data is temporal
    --instead of an INT.
     SELECT CONVERT(CHAR(7),DATEADD(mm,1,@BetterYrMo),121) --CONVERT only for display purposes here.
    ;

    We call them "data type spastic" and "numerically inward focused", just like waiting for a coherent response from management, don't hold the breath because it's an oxymoron!
    😎

    Sorting and grouping by numerical date values may have some benefits but any type of calculations and manipulations will suffer big time!

  • Jeff Moden

    SSC Guru

    Points: 996619

    Eirikur Eiriksson - Sunday, August 12, 2018 8:55 AM

    Jeff Moden - Saturday, August 11, 2018 4:24 PM

    UGH!  This is what happens when people try to be "smart" about how to store dates and times except for maybe in a Calendar table.  This problem would be a cake walk if the data were stored correctly for processing rather than some poor attempt to save 2 or 4 bytes of space or saving the display value instead of the real value.


    --===== If this were in a table, you could add a constraint to accept only first-of-month dates.
    DECLARE @BetterYrMo DATE = '20150101' --OR DATETIME OR SMALLDATETIME OR even DATETIME2
    ;
     SELECT CONVERT(CHAR(6),DATEADD(mm,1,@BetterYrMo),112) --CONVERT only for display purposes here.
    ;
    --If they suddenly want a DASH between year and month, it's easy if the base data is temporal
    --instead of an INT.
     SELECT CONVERT(CHAR(7),DATEADD(mm,1,@BetterYrMo),121) --CONVERT only for display purposes here.
    ;

    We call them "data type spastic" and "numerically inward focused", just like waiting for a coherent response from management, don't hold the breath because it's an oxymoron!
    😎

    Sorting and grouping by numerical date values may have some benefits but any type of calculations and manipulations will suffer big time!

    You already know this so just saying it out loud for anyone else that reads this that may be unaware...

    GROUPing is a calculation.  If someone decides that they also want to see SUMs (for example) by QUARTER and YEAR, having dates stored in the YYYYMM format is going to cause the big time suffering that you speak of.

    --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)
    F

  • Luis Cazares

    SSC Guru

    Points: 183633

    Eirikur Eiriksson - Friday, August 10, 2018 11:13 PM

    texpic - Friday, August 10, 2018 7:45 PM

    Geez, I'm stoopid, LOL  ..this works but  is there a better way

    DECLARE @yrMo int = 201501

    select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)

    This will break 5 times for every year, in the months that have less than 31 days!
    😎

    Here is a better method, which does the calculation correctly and avoids the string conversion.

    DECLARE @YRMO INT = 201501;
    DECLARE @MSHIFT INT = 1;

    SELECT DATEADD(MONTH,((@YRMO % 100) -1) + @MSHIFT,DATEADD(YEAR,((@YRMO / 100) - 1900),0)) AS THE_DATE

    It actually won't break because it doesn't matter if the day is the first or the third or some other day. In the end, the day is truncated.
    I'm not sure about speed, but this might be simple for anyone to figure out what the code is doing and still use always the first day of the month.

    DECLARE @yrMo int = 201501;

    SELECT  CONVERT(char(6),DATEADD( MM, 1, CONCAT( @yrMo,'01')), 112);

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • sgmunson

    SSC Guru

    Points: 110459

    Luis Cazares - Monday, August 13, 2018 7:21 AM

    Eirikur Eiriksson - Friday, August 10, 2018 11:13 PM

    texpic - Friday, August 10, 2018 7:45 PM

    Geez, I'm stoopid, LOL  ..this works but  is there a better way

    DECLARE @yrMo int = 201501

    select CONVERT(varchar(6),CONVERT(datetime, CONCAT(@yrMo,'01'), 112)+31,112)

    This will break 5 times for every year, in the months that have less than 31 days!
    😎

    Here is a better method, which does the calculation correctly and avoids the string conversion.

    DECLARE @YRMO INT = 201501;
    DECLARE @MSHIFT INT = 1;

    SELECT DATEADD(MONTH,((@YRMO % 100) -1) + @MSHIFT,DATEADD(YEAR,((@YRMO / 100) - 1900),0)) AS THE_DATE

    It actually won't break because it doesn't matter if the day is the first or the third or some other day. In the end, the day is truncated.
    I'm not sure about speed, but this might be simple for anyone to figure out what the code is doing and still use always the first day of the month.

    DECLARE @yrMo int = 201501;

    SELECT  CONVERT(char(6),DATEADD( MM, 1, CONCAT( @yrMo,'01')), 112);

    Luis, what Eirikur is saying breaks is adding 31 days, especially if one happens to start on January 28th.   Using actual date math with DATEADD solves that problem, and that's all that Eirikur did.   Getting into the habit of using character-based date manipulation may work if you are anally consistent in being sure your calculation is correct, but one slip and kaboom!   Much safer to stick entirely with tested date math and DATEADD.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Luis Cazares

    SSC Guru

    Points: 183633

    sgmunson - Tuesday, August 14, 2018 9:27 AM

    Luis, what Eirikur is saying breaks is adding 31 days, especially if one happens to start on January 28th.   Using actual date math with DATEADD solves that problem, and that's all that Eirikur did.   Getting into the habit of using character-based date manipulation may work if you are anally consistent in being sure your calculation is correct, but one slip and kaboom!   Much safer to stick entirely with tested date math and DATEADD.

    But it will never start on January 28th, as the OP is using 01 as the day. I do agree that tested date math and DATEADD/DATEDIFF are much better options.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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