THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Some date math fun

Note: This concept presented is pretty simple and I am not claiming that I am the first to do this… so if you were the one who came up with this, let me know and I will give you linkage

Today I was needing to get the data for the current month for a query, and it hit me that I really didn’t have a good way to do this.  There were two common methods that people use:

WHERE YEAR(MonthColumn) = YEAR(Getdate()) AND MONTH(MonthColumn) = MONTH(Getdate())

But this particular method is pretty horrible because it makes it virtually impossible for the optimizer to use any indexes for the operation.  The other common way to do this is also sort of hokey, consisting of creating a date value out of text strings that represent the first of the month at midnight…

WHERE MonthColumn >= CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + RIGHT('0' + CAST(Month(GETDATE()) AS VARCHAR(2)),2) + '01' as datetime)

So I do a quick surf around the web to see if I could find anything do this with.  So I found a site with what seemed like lots of good ones, but in the comments they claimed that the the functions were broken for Leap Years…Then I got to thinking, how better to do this… So I grabbed the code snippet for the common way to strip time from a date string:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <dateValue>))

The solution

This works by getting the number of days since day 0 (which is 19000101 when casted to a datetime or smalldatetime) used, so we can add a given number of days to this value to get a new position.  After a few minutes of pondering (aka, websurfing), it seemed to me that the key was adding a factor to the DATEDIFF value. Adding or subtracting days from that value would be the way that makes most sense to get to the first day of the month, the last day of the month, year, decade, whatever. 

DATEADD(day, 0, DATEDIFF(day, 0, <datevalue> ) + <factor>)

So by varying the factor, you could move from the current day to the beginning of the month by subtracting (adding a negative) the day number of the month (plus one, or you get the last day of the previous month):

DATEADD(day, 0, DATEDIFF(day, 0, <datevalue> ) - DATEPART(DAY,<datevalue>) + 1)

So, let’s say it is 20110511 (the day SQL Rally starts and I go on vacation for 10 days). Breaking it down we get:

DATEADD(day, 0, DATEDIFF(day, 0,  '20110511') - DATEPART(DAY,'20110511') + 1)
DATEADD(day, 0,  (40672 – 11 + 1))
DATEADD(day, 0,  (40662)) -- this statement converts the value back to a datetime datatype value
2011-05-01 00:00:00.000

This works fine for the as long as you vary the factor from the current day, but to get interesting results, the next concept is to move the primary datevalue around to get the start of the next month, the start and end of the year, etc. Of course, what complicates this a bit is leap year, so you have to be very careful about where you go from here. This method will work in all cases to to get the first day of the month, so if I want to get the end of the month, I can just add a month to the value, less one so you get the last day of this month, not the first day of the next month..

DATEADD(month, 1, DATEDIFF(day, 0, <datevalue> ) - DATEPART(DAY,<datevalue>) )

This works because the DATEADD(day,0 was only there to convert the number back to a date, and the DATEADD function will convert the numeric value to a date, then add a month.

Finally, getting the first day of the year is equally pretty easy, by just subtracting the day of the year from the current day value, again plus one because you would end up with the first day of the previous year.

DATEADD(day, 0, DATEDIFF(day, 0, <datevalue> ) - DATEPART(dayofyear,<datevalue> ) + 1)

Just as in previous calculations, now that you have the start of the current year, you can get the the last day of this year by adding a year to that value, but not adding the 1, which gives you the start of next year:

DATEADD(year, 1, DATEDIFF(day, 0, <datevalue> ) - DATEPART(dayofyear,<datevalue> ) )

And finally, the first day of the previous year by varying the DATEADD calculation:

DATEADD(year, -1, DATEDIFF(day, 0, <datevalue> ) - DATEPART(dayofyear,<datevalue> )  + 1)

Where to get the code

On my drsql.org website, I have a page called: Code Snippets (http://drsql.org/codesnippets.aspx). It is where I have started a list of those little bits of code that you just need all of the time. Not full modules of code, but just stuff that annoyed me regularly because I didn’t remember how to do it off the top of my head.  The code for this and a few other snippets can be found there.  I went wit the common solutions that I find myself needing, first and last day of the month were the ones that really got me considering needing a solution, but also some code to implement a sequence in code, using a CTE.

Testing

Using a calendar table (I will demonstrated how you can test date functions using the sequence code mentioned in the previous section), I took each of the functions and tested them for the various types of years we have. Leap years, non-leap years, and those weird outliers like 1900 that isn’t a leap year because it is divisible by 100, but is a leap year when divisible by 1000, as in the year 2000 (I think the people who thought of this calendar were consultants.) In my process, there are two phases to testing. Proof and Validation. It is a topic I will write more about as I progress with the book, but first you manually trace through and explain the code to yourself (I like to blog new generic bits of code I write.. I found errors in my original code for this edition that way initially.)

The next step is validation or unit testing. For this type of code, I just set up a query to view date values for validation.  If this was part of a larger system of code that would likely get modified, I would write statements that validated lots of values by writing code like:

SELECT CASE WHEN DATEADD(month, 1, DATEDIFF(day, 0, '20120229') - DATEPART(DAY,'20120229') ) = '20120229' THEN 'PASS' ELSE 'FAIL' END

But for code snippets this becomes tedious and slow.  So for this case, I just wrote the following select statement that generates values and tests the algorithm

;with digits (i) as(
                             select 1 as i union all  select 2 as i union all select 3 union all
                             select 4 union all select 5 union all select 6 union all select 7 union all
                             select 8 union all select 9 union all select 0)
,sequence (i) as (
    SELECT  D1.i + (10*D2.i)+ (100*D3.i)+ (1000*D4.i)+ (10000*D5.i)
    FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3 CROSS JOIN digits AS D4 CROSS JOIN digits as D5 )
, calendar AS
(select DATEADD(DAY,i,'18900101') AS DateValue,
        MONTH( DATEADD(DAY,i,'18900101')) AS Month,
        YEAR( DATEADD(DAY,i,'18900101')) AS Year,
        Day( DATEADD(DAY,i,'18900101')) AS Day
from sequence )
SELECT dateValue,
DATEADD(day, 0, DATEDIFF(day, 0, datevalue ) - DATEPART(DAY,datevalue) + 1)  AS firstDayOfMonth,
                  DATEADD(month, 1, DATEDIFF(day, 0, datevalue ) - DATEPART(DAY,datevalue) ) AS lastDayofMonth,
                  DATEADD(month, 1, DATEDIFF(day, 0, datevalue ) - DATEPART(DAY,datevalue) + 1) AS firstDayofNextMonth,
                  DATEADD(day, 0, DATEDIFF(day, 0, datevalue ) - DATEPART(dayofyear,datevalue ) + 1) AS firstDayOfYear,
                  DATEADD(year, 1, DATEDIFF(day, 0, datevalue ) - DATEPART(dayofyear,datevalue ) ) AS lastDayOfYear
FROM   calendar A
--WHERE year = '1900'
--WHERE year = '2000'
WHERE year = '2012'
ORDER  BY dateValue

Published Wednesday, February 09, 2011 12:01 AM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Aaron Bertrand said:

Remember that if you use the new date/time types in 2008 that the shorthand + and - for adding/subtracting days no longer works.

February 9, 2011 7:42 AM
 

drsql said:

Good point.  It doesn't affect this code but this code does work because it does the implicit because I don't do math like that on the date values.  I tested passing it date and datetime2 type values to make sure.

It does end up with a dateadd function on an integer from the datediff and datepart results, so the dateadd returns a datetime. So it could mean that using this code as a search argument for a newer datetime might require a cast to the proper datatype.  

February 9, 2011 12:01 PM
 

Dave J said:

Of course, to get the first day of the current month (or any month relative to it), the following will suffice:

DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)

You can do the same for the year.

February 11, 2011 10:23 AM
 

Andrew Pascal said:

This is probably the best solution I have seen to this problem. Although elegant compared to other solutions (respect to you for that), it's still hard to remember how to write it and far from obvious to the reader what all those nested DATEADDs and DATEPARTs and DATEDIFFs are doing.

Date arithmetic is an area where SQL Server regularly comes up short. I would personally create functions for each of these operations (remove time portion, remove date portion, find first/last day of month/year etc) and include them in every SQL Server instance. This would save repeating this unwieldy and unintuituve chunk of code.

Even better would be if equivalent functions were built into a future release of SQL Server (are you listening. Microsoft?) :-)

February 14, 2011 4:48 AM
 

pRamster said:

To find the beginning of the month, it is easier (to my thinking anyway) to use the form:

DateAdd(month, DateDiff(month, 0, <datevalue>), 0)

rather than:

DATEADD(day, 0, DATEDIFF(day, 0, <datevalue> ) - DATEPART(DAY,<datevalue>) + 1) as you suggest.

Not sure if there is a significant performance impact in losing the DATEPART call, though.

However, the critical part of this discussion is that we should ensure that the final query uses any available indexes.

WHERE DateAdd(month, DateDiff(month, 0, <datevalue>), 0) = DateAdd(month, DateDiff(month, 0, GETDATE()), 0)

will find all instances where <datevalue> is the current month, but it will not use an index on <datevalue> to do so.

WHERE <datevalue> BETWEEN DateAdd(month, DateDiff(month, 0, GETDATE()), 0) AND DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, GETDATE()) + 1, 0))

however, WILL use an index on <datevalue> to determine all instances where <datevalue> is in the current month, and this is the important point.

February 14, 2011 5:51 AM
 

Dave Jackson said:

dug this out of the toolbox, just uses date functions with no - or + shorthand.

HTH

declare @TruncDate datetime

set @TruncDate=Dateadd(DAY, 0, Datediff(DAY, 0, GetDate()))

select

Dateadd(Month,-1,Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate)) as FirstDayPrevMonth,

Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate) as FirstDayThisMonth,

Dateadd(Month,1,Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate)) as FirstDayNextMonth,

Dateadd(Day,-Datepart(day,@TruncDate),@TruncDate) as LastDayPrevMonth,

Dateadd(Day,-1,Dateadd(Month,1,Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate))) as LastDayThisMonth,

Dateadd(Day,-1,Dateadd(Month,2,Dateadd(Day,-Datepart(day,@TruncDate)+1,@TruncDate))) as LastDayNextMonth

February 16, 2011 7:18 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement