Return First and Last Day of Previous Month

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.

  1. 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.
  2. 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]
    
  3. 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

6 Comments

6 Responses

  1. jamoldover says:

    Here’s an alternate method for this that I’ve been using for a few years (that eliminates the issue of how many days the previous month has). The starting date is determined by simply subtracting a month from the current date, then forcing it to the 1st of the month; the ending date is determined by taking the first day of the current month, and subtracting 1 day.
    SELECT DATEADD([MONTH], – 1, CONVERT (DATETIME, CONVERT (VARCHAR(2), MONTH(GETDATE())) + ‘/1/’ + CONVERT (VARCHAR(4), YEAR(GETDATE())))) AS StartDate, THEN DATEADD([DAY], – 1, CONVERT (DATETIME, CONVERT (VARCHAR(2), MONTH(GETDATE())) + ‘/1/’ + CONVERT (VARCHAR(4), YEAR(GETDATE())))) AS EndDate

    I’ve tested this with SQL 2000 all the way up to SQL 2008R2, and there’s no reason I can see that it wouldn’t work with later versions as well.

  2. jamoldover says:

    Oops – typo. The “THEN” that starts the code for the ending data needs to be removed.

  3. twyw says:

    I am curious about this as I do a lot of queries that involve the first or last date of the month.

    This is my trusty code snippet:


    -- prev month
    SELECT DATEADD(m,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))
    SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    -- current month
    SELECT DATEADD(m,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
    SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

  4. rpohorely says:

    Hello,
    try this:
    [sourcecode language=’sql’]
    DECLARE @date date = GETDATE()

    SELECT
    CAST(LEFT(CONVERT(nvarchar, DATEADD(M, -1, @date), 112), 6) + ’01’ AS date) AS first_day_of_prev_month
    ,DATEADD(D, -1, CAST(LEFT(CONVERT(nvarchar, @date, 112), 6) + ’01’ AS date)) AS last_day_of_prev_month
    [/sourcecode]

    -RP

  5. sgtmango333 says:

    Your code seems like the long way around the barn. The quickest way to find the first and last days, I’ve found is to just add a month and subtract a day.

    declare
    @thisDate DateTime
    , @thisMonth char(2)
    , @thisYear char(4)
    , @firstDay DateTime
    , @lastDay DateTime

    SET @thisDate = GETDATE()

    SET @thisMonth = DATEPART(MONTH, @thisDate)
    SET @thisYear = DATEPART(YEAR, @thisDate)

    select @thisDate, @thisMonth

    SET @firstDay = @thisYear+'-'+@thisMonth+'-01'
    SET @lastDay = DATEADD(DAY,-1,(DATEADD(MONTH,1,@firstDay)))

    SELECT @firstDay, @lastDay

  6. Dave says:

    Thanks for the comments. I love it when I build a wheel out of square blocks.

    @twyw… interesting use of adding 0 in DATEADD, and the alternating use of “m” and “mm”, I assume for readability purposes.

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>