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