get the first and last day of any Year/Month

  • Hi,

    i have a view with two columns, lets say SpecYear and SpecMonth, both are integer.

    How can I build two new columns with the first and last day of this year/month:pinch:

    ex.

    2013 02 => 2013-02-01 2013-02-28

    etc.

    thanx

    --
    candide
    ________Panta rhei

  • candide (2/25/2013)


    Hi,

    i have a view with two columns, lets say SpecYear and SpecMonth, both are integer.

    How can I build two new columns with the first and last day of this year/month:pinch:

    ex.

    2013 02 => 2013-02-01 2013-02-28

    etc.

    thanx

    This will work.

    declare @Month int = 2, @Year int = 2013

    declare @ThisDate datetime

    set @ThisDate = cast(@Year as char(4)) + right('0' + cast(@Month as varchar(2)), 2) + '01'

    select @ThisDate

    select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month

    select dateadd(day, -1, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) -- End of this month

    Take a look at Lynn's article for a number of datetime routines here. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]

    If at all possible you should consider storing datetime information as datetime instead of multiple integer columns.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This will work:

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SET @ThisYear = 2013;

    SET @ThisMonth = 2;

    SELECT

    DATEADD(MONTH, @ThisMonth - 1, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME))) BOM,

    DATEADD(DAY, -1, DATEADD(MONTH, @ThisMonth, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME)))) EOM

  • You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SELECT @ThisYear = 2013,

    @ThisMonth = 2;

    SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),

    EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)

    Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SELECT @ThisYear = 2012, --Leap Year!

    @ThisMonth = 2;

    SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),

    EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)

    Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.

    --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.


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

  • Hi,

    Jeff's solution works great:-P

    exactly what I needed

    thanx

    --
    candide
    ________Panta rhei

  • And another alternative solution, based on 'strings'

    declare @Month int = 2, @Year int = 2013

    declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)

    select CONVERT(varchar(7),@thisdate,121)+'-01'

    select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'

    ben brugman

  • ben.brugman (2/26/2013)


    And another alternative solution, based on 'strings'

    declare @Month int = 2, @Year int = 2013

    declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)

    select CONVERT(varchar(7),@thisdate,121)+'-01'

    select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'

    ben brugman

    Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.

    --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.


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

  • candide (2/26/2013)


    Hi,

    Jeff's solution works great:-P

    exactly what I needed

    thanx

    Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it. Do you understand how and why it works?

    --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.


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

  • The integer value for the first day of the month is even easier:

    SELECT BOM = @ThisYear * 10000 + @ThisMonth * 100 + 1,

    but you must CAST it to char(8) before storing it in a date/datetime column.

    DECLARE @BOM datetime

    SELECT @BOM = CAST(@ThisYear * 10000 + @ThisMonth * 100 + 1 AS char(8))

    SELECT @BOM

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Jeff Moden (2/25/2013)


    You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SELECT @ThisYear = 2013,

    @ThisMonth = 2;

    SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),

    EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)

    Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SELECT @ThisYear = 2012, --Leap Year!

    @ThisMonth = 2;

    SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),

    EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)

    Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.

    That formula was a bit of a joint effort that Peter Larsson and I developed on this thread:

    Make Date function (like in VB)

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

  • Michael Valentine Jones (2/26/2013)


    Jeff Moden (2/25/2013)


    You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula is 1900-01-01. The "-1" in the EOM formula is the day before that. Because of the integer math, it's very fast. I can't remember if it was Michael Valentine Jones or Peter Larsson that I first saw with this forumula.

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SELECT @ThisYear = 2013,

    @ThisMonth = 2;

    SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),

    EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth ,-1)

    Of course, the "-1" in the BOM formula can be distributed to the other constant to make the formula a bit shorter, still.

    DECLARE @ThisYear INT,

    @ThisMonth INT;

    SELECT @ThisYear = 2012, --Leap Year!

    @ThisMonth = 2;

    SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),

    EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)

    Both will also work correctly for dates before 1900 without modification and Leap Years are also figured correctly.

    That formula was a bit of a joint effort that Peter Larsson and I developed on this thread:

    Make Date function (like in VB)

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

    Thanks, Michael. This time, I'm going to add that URL to the code snippet.

    --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.


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

  • Just to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.

    Here's the test data. Since all of the code under test is so very fast on today's machines, I made 10 Million rows of test data. If you do, in fact, have a fairly recent machine, the table will only take about 12 seconds to be built and populated.

    --===== Conditionally drop the test table to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL

    DROP TABLE #TestTable

    ;

    --===== Create and populate the test table on-the-fly.

    SELECT TOP 10000000

    TheYear = ABS(CHECKSUM(NEWID()))%14+2000,

    TheMonth = ABS(CHECKSUM(NEWID()))%12+1

    INTO #TestTable

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    GO

    Here are the actual tests. For those not familiar with the "@BitBucket" style of testing, the variable captures the result of the calculation and dumps it to take the display time and unnecessary disk processing time out of the picture so we're measuring just the result of the formulas/methods.

    RAISERROR('========== INTEGER MATH Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket DATETIME;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = DATEADD(mm, TheYear*12-22801+TheMonth, 0)

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    RAISERROR('========== Lynn''s Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket DATETIME;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = DATEADD(MONTH, TheMonth - 1, DATEADD(YEAR, TheYear - 1900, CAST('19000101' AS DATETIME)))

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    RAISERROR('========== Scott''s INT Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket int;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = TheYear * 10000 + TheMonth * 100 + 1

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    RAISERROR('========== Scott''s IMPLICIT STRING Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket DATETIME;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = CAST(TheYear * 10000 + TheMonth * 100 + 1 AS CHAR(8))

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    Here are the results from my laptop (I5 4 core 64 bit processor running at 2.5 GHz with 6GB RAM).

    ========== INTEGER MATH Conversion ==============================

    SQL Server Execution Times:

    CPU time = 3151 ms, elapsed time = 3146 ms.

    ========== Lynn's Conversion ==============================

    SQL Server Execution Times:

    CPU time = 3229 ms, elapsed time = 3241 ms.

    ========== Scott's INT Conversion ==============================

    SQL Server Execution Times:

    CPU time = 2262 ms, elapsed time = 2253 ms.

    ========== Scott's IMPLICIT STRING Conversion ==============================

    SQL Server Execution Times:

    CPU time = 6318 ms, elapsed time = 6313 ms.

    A couple of points to bring up here.

    First, I only used the code examples that were easy to convert to a full table test. It is enough, I believe, to drive the main point home.

    As long as Lynn's good code looks, he wisely stuck to integer math and the intrinsic date/time functions. Because of that, his code is nearly as fast as the Integer Math method.

    If you look at Scott's INT conversion method, it absolutely smokes everything else. Still, we end up with an integer date, which may not be what you need, but it works VERY well for doing things like outputing ISO dates to a file.

    Now, the main point. To be sure, this has nothing to do with Scott's abilities and I'm not picking on him. He did, after all, write some of the most compact code there is for the tassk. He just happens to have written the best code to make the point. The only difference between Scott's INT conversion and his conversion to datetime is the explicit conversion to a string and the implict conversion to DATETIME. Both require a string conversion. As you can see, the string conversion code is more that twice as slow as the Integer Math conversion and nearly 3 times slower than the pure INT conversion. Yes, I have to agree with you that a lousy 3 second difference across 10 Million rows doesn't seem like much but, consider this... how much would you have to spend on hardware to double the speed of your code?

    If you double the speed of all of your code just by using simple tricks like avoiding string conversions in DATETIME calculations, just imagine how fast your apps would actually run. You could finally unplug the garden-hose from your CPU coolers. 😛

    Like Granny used to say... "Mind the pennies and the dollars will take care of themselves." 😀

    --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.


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

  • Jeff Moden (2/26/2013)


    candide (2/26/2013)


    Hi,

    Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it. Do you understand how and why it works?

    Jeff,

    I searched some time for a solution, but most answers work with string handling like 'CAST blabla varchar blabla' which may work but is not what I think of adjacent handling of date values. A numeric solution like yours I never saw before, and it's simple and fast. Nothing more to say...

    Waiting for cool date values handling in next SQL Server version...:-)

    --
    candide
    ________Panta rhei

  • Jeff Moden (2/26/2013)


    Just to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.

    Here's the test data. Since all of the code under test is so very fast on today's machines, I made 10 Million rows of test data. If you do, in fact, have a fairly recent machine, the table will only take about 12 seconds to be built and populated.

    --===== Conditionally drop the test table to make reruns in SSMS easier.

    IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL

    DROP TABLE #TestTable

    ;

    --===== Create and populate the test table on-the-fly.

    SELECT TOP 10000000

    TheYear = ABS(CHECKSUM(NEWID()))%14+2000,

    TheMonth = ABS(CHECKSUM(NEWID()))%12+1

    INTO #TestTable

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    GO

    Here are the actual tests. For those not familiar with the "@BitBucket" style of testing, the variable captures the result of the calculation and dumps it to take the display time and unnecessary disk processing time out of the picture so we're measuring just the result of the formulas/methods.

    RAISERROR('========== INTEGER MATH Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket DATETIME;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = DATEADD(mm, TheYear*12-22801+TheMonth, 0)

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    RAISERROR('========== Lynn''s Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket DATETIME;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = DATEADD(MONTH, TheMonth - 1, DATEADD(YEAR, TheYear - 1900, CAST('19000101' AS DATETIME)))

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    RAISERROR('========== Scott''s INT Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket int;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = TheYear * 10000 + TheMonth * 100 + 1

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    RAISERROR('========== Scott''s IMPLICIT STRING Conversion ==============================',0,1) WITH NOWAIT;

    DECLARE @Bitbucket DATETIME;

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = CAST(TheYear * 10000 + TheMonth * 100 + 1 AS CHAR(8))

    FROM #TestTable;

    SET STATISTICS TIME OFF;

    GO

    Here are the results from my laptop (I5 4 core 64 bit processor running at 2.5 GHz with 6GB RAM).

    ========== INTEGER MATH Conversion ==============================

    SQL Server Execution Times:

    CPU time = 3151 ms, elapsed time = 3146 ms.

    ========== Lynn's Conversion ==============================

    SQL Server Execution Times:

    CPU time = 3229 ms, elapsed time = 3241 ms.

    ========== Scott's INT Conversion ==============================

    SQL Server Execution Times:

    CPU time = 2262 ms, elapsed time = 2253 ms.

    ========== Scott's IMPLICIT STRING Conversion ==============================

    SQL Server Execution Times:

    CPU time = 6318 ms, elapsed time = 6313 ms.

    A couple of points to bring up here.

    First, I only used the code examples that were easy to convert to a full table test. It is enough, I believe, to drive the main point home.

    As long as Lynn's good code looks, he wisely stuck to integer math and the intrinsic date/time functions. Because of that, his code is nearly as fast as the Integer Math method.

    If you look at Scott's INT conversion method, it absolutely smokes everything else. Still, we end up with an integer date, which may not be what you need, but it works VERY well for doing things like outputing ISO dates to a file.

    Now, the main point. To be sure, this has nothing to do with Scott's abilities and I'm not picking on him. He did, after all, write some of the most compact code there is for the tassk. He just happens to have written the best code to make the point. The only difference between Scott's INT conversion and his conversion to datetime is the explicit conversion to a string and the implict conversion to DATETIME. Both require a string conversion. As you can see, the string conversion code is more that twice as slow as the Integer Math conversion and nearly 3 times slower than the pure INT conversion. Yes, I have to agree with you that a lousy 3 second difference across 10 Million rows doesn't seem like much but, consider this... how much would you have to spend on hardware to double the speed of your code?

    If you double the speed of all of your code just by using simple tricks like avoiding string conversions in DATETIME calculations, just imagine how fast your apps would actually run. You could finally unplug the garden-hose from your CPU coolers. 😛

    Like Granny used to say... "Mind the pennies and the dollars will take care of themselves." 😀

    Perhaps. But I think my code is instantly understandable when read. So unless you know or expect to be processing huge number of rows, developer time is multiple orders of magnitude more expensive than computer clock-time seconds.

    Btw, weren't you the one that did testing that demonstrated that SET STATISTICS TIME ON itself caused, for example, scalar functions to appear worse than they really are? I'm not saying it's known, but isn't it possible that the process itself of determining the time used caused some of the spike in time? Although I know all string manipulation/handling in SQL Server is relatively extremely slow.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology :hehe: (plus it doesn't use some strange number of 22800):

    selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM

    selectdateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

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

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