I had a colleague approach me today with a production issue. The base challenge was to produce a query that will return the first and last day of the previous month. When I reviewed the query that he provided (ie., the one that was in the production code) I quickly determined that it was too complicated for the amount of work it needed to do. Worst yet, the results it produced were wrong, some of the time.
So I set out to re-write it “my way”.
If you don’t want to read the details of how this works let me just give you the query that I created. The following code snippet will return the first and last day of the previous month, for each day in 2014.
[sourcecode language='sql' padlinenumbers='true' wraplines='true' htmlscript='false'] use tempdb; go CREATE TABLE #mydates (mydatetime datetime , startPrevMonth datetime , endPrevMonth datetime , startdateid int , enddateid int ) truncate table #mydates; declare @myDatetime datetime; set @mydatetime = '1/1/2014'; WHILE @mydatetime < '1/1/2015' BEGIN INSERT INTO #mydates SELECT @mydatetime AS myTime , d.Start_of_Prev_Month , d.End_of_Prev_Month , d1.date_id AS Start_of_Prev_Month_ID , d2.date_id AS End_of_Prev_Month_ID FROM ( SELECT DATEADD(day , (DATEPART(day, (DATEADD(month, -1, @mydatetime) )) * -1) + 1 , DATEADD(month, -1, (cast(cast(@myDatetime AS DATE) AS datetime))) ) AS Start_Of_Prev_Month , DATEADD(ms, -2, (DATEADD(month, 1, (DATEADD(day , (DATEPART(day, (DATEADD(month, -1, @mydatetime) )) * -1) + 1 , DATEADD(month, -1, (cast(cast(@myDatetime AS DATE) AS datetime))) ))))) AS End_Of_Prev_Month ) d INNER JOIN GMCR_Mart.dbo.d_date d1 ON d1.the_date = d.Start_of_Prev_Month INNER JOIN GMCR_Mart.dbo.d_date d2 ON d2.the_date = CAST(d.End_Of_Prev_Month AS DATE); SET @myDatetime = DATEADD(day, 1, @myDatetime) END select * from #mydates [/sourcecode]
There were some interesting findings while developing this simple query.
- Incorrect results were returned with the initial cut at this query when the month we were looking at had more days then the previous month. For example, March has 31 days, but February has either 28 or 29 days. July has 31 days but June has 30. The same holds true for Oct/Sept and Dec/Nov. While January has 31 days, so does December, so there were no issues encountered.
- The SQL DATEADD function is pretty intelligent. If we are subtracting a month from March 29, it will return February 28 (for non-leap years). It will also return February 28th for March 30 and 31. This is a definite plus… somewhat obvious. But if it didn’t work as it does we would have other problems. The following queries all return Feb 28, 2014:
[sourcecode language='sql' ] select dateadd(month, -1, '3/28/2014') select dateadd(month, -1, '3/29/2014') select dateadd(month, -1, '3/30/2014') select dateadd(month, -1, '3/31/2014') [/sourcecode]
- One of the tasks we needed to do was to strip out the time component of a datetime data type (I know, deprecation city. Developer habits are tough to change.). Itzek Ben-Gan’s T-SQL Fundamentals (SQL 2008) has a nifty query that subtracts the number of days since a reference date. Here’s an example of his code. Although it is simple enough, it’s not obvious what it does if you are looking at it quickly.
[sourcecode language='sql' ] select dateadd (day, datediff(day, '20010101', '3/29/2014 10:24:54.123'), '20010101') [/sourcecode]
A more intuitive approach is to cast the date/time to a DATE datatype, then back to the datetimeX datatype. Of course, this approach will not work for SQL2005 and older.
[sourcecode language='sql' ] select cast(cast('3/29/2014 10:24:54.123' AS DATE) AS datetime) [/sourcecode]
Now, for the interesting part. What I discovered from my initial query (aka, not correct) was that when we had a 31 day month that was preceded by a 28 or 30 day month, the previous month’s begin and end dates returned were incorrect.
Here is what the original query looked like (returns just the previous month’s first day… the last day had the same problem):
[sourcecode language='sql' ] -- Wrong way!!! select DATEADD( day , (DATEPART(day, '3/29/2014' ) * -1) + 1 --********** WRONG!!! Don't subtract 29 days from a 28 day month! ******* , DATEADD(month, -1, (cast(cast('3/29/2014 10:24:54.123' AS DATE) AS datetime))) ) [/sourcecode]
This query will return “2014-01-31 00:00:00.000”. Obviously this is not the first day of the previous month, relative to March 29th. When subtracting 29 days from a date in February, you are suddenly in January. Go figure…
Hopefully the code snippets in this post come out ok. I’m a little rusty at posting code; and thhis is a new word-press plug-in.
Thanks to Rohit Shinde for bringing this fun puzzle to my attention.
Stay curious; keep learning