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;

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'

	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
													, (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)


select * from #mydates

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')
  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')


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)


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

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

    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

    -- current month

  4. rpohorely says:

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

    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


  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.

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

top personal essay ghostwriting services online custom definition essay editing for hire for phd best admission essay on hacking top research proposal editing service gb esl masters essay proofreading sites for mba best school essay editing website usa cheap dissertation introduction ghostwriters site uk popular university thesis example professional dissertation abstract editor website for school popular school paper example top bibliography ghostwriters services ca best masters essay proofreading services for university best letter writer websites for university esl school personal statement topic top reflective essay ghostwriting website usa top article writing sites usa richard rodriguez aria analysis birthday essay write me anthropology case study best bibliography editing websites online cheap university essay editor services au definition essay ghostwriters services us professional descriptive essay proofreading for hire for school esl case study writing websites for phd popular dissertation chapter writer website for school professional homework editor site top research proposal proofreading websites for mba best definition essay editor service for phd tom and daisy esl critical essay writers for hire uk best college assignment examples phd ghostwriters service gb top critical analysis essay writing service online best creative writing editor website au esl dissertation hypothesis ghostwriters for hire online popular mba course work topics march 1917 russian revolution esl cover letter proofreading websites for college pay to get government application letter pay to do culture report cheap literature review ghostwriters for hire ca professional descriptive essay writing websites online custom home work editor websites for university popular mba essay ghostwriters website gb cheap papers writing website phd essay writers services uk buy esl essay online esl persuasive essay editor services online type my cheap biography write cheap best essay on brexit cheap admission paper writing site for school esl phd essay writing service usa cheap masters phd essay assistance professional masters movie review assistance professional critical essay writers websites uk top dissertation abstract ghostwriter site for school home work editing service us cheap creative writing editing service online type my custom creative essay on hacking pay to write management movie review esl homework editor for hire ca popular admission paper writing services us popular custom essay writer website us letter writer for hire online professional dissertation abstract ghostwriters for hire au analysis ghostwriting site us pay to get top school essay on brexit judge danforth best phd critical essay assistance physical activity essay esl phd essay proofreading service uk best mba persuasive essay topics popular literature review editor websites ca essay ghostwriter for hire online pay for my management cv esl problem solving ghostwriter service for mba buy art & architecture case study popular blog editor sites for phd dissertation hypothesis editor websites uk top dissertation introduction ghostwriters service stranger in the village summary popular cv proofreading websites online type my health content custom personal statement proofreading service gb pay for my cheap college essay on hillary custom mba essay proofreading for hire us custom dissertation methodology editing services au dissertation results editing websites online best assignment proofreading website for phd sample obituary for father top thesis writers services for university do my leadership letter cheap university essay writing service uk cheap university college essay help geology essay writers website esl resume editor site gb esl research proposal writing websites popular assignment editor website for masters best research paper writing website usa best cover letter proofreading service ca school uniforms are a bad idea professional scholarship essay proofreading site for masters custom bibliography writers site for phd custom custom essay editor services au best term paper editing website for phd top biography writer services for school professional writers service for mba popular critical thinking editing for hire ca best dissertation conclusion writer site gb top dissertation abstract proofreading for hire for university analysis essay on anthropology cheap thesis statement ghostwriter website for mba best expository essay writing sites ca cheap argumentative essay ghostwriter site for college esl phd essay editing sites online top expository essay proofreading sites gb learning to read and write by frederick douglass essay custom reflective essay editor websites esl application letter ghostwriting site for college help me write custom best essay descriptive writing site ca popular admission paper writing services ca esl blog post proofreading site for masters custom cv proofreading site for university political culture can be defined as the write earth science essays pay for professional personal essay on lincoln cheap cheap essay ghostwriters website online help writing management biography professional critical essay editor sites us esl school paper assistance movie review writer website usa top book review editor websites uk esl expository essay ghostwriter for hire for university esl essays editor websites for masters top paper ghostwriters for hire for college pay to get critical essay on hillary esl critical analysis essay ghostwriters service online write me economics homework business ghostwriting site do my algebra personal statement type my esl college essay on shakespeare best content writer site for mba top book review editor for hire usa esl movie review proofreading service for phd esl college essay ghostwriter websites for masters analysis of a movie type my art & architecture dissertation methodology popular analysis essay ghostwriting service for masters mercutio personality traits popular school speech sample order poetry resume top definition essay editing website for school esl phd dissertation proposal example cheap rhetorical analysis essay editor websites for school popular custom essay editing site gb popular cover letter ghostwriter sites for college cheap persuasive essay writing website usa popular case study writing websites for phd top dissertation ghostwriting site for mba best business plan editing website usa best book review proofreading sites for school type my music dissertation chapter cheap curriculum vitae ghostwriters service for mba pay for my professional analysis essay transcendentalist essay popular university essay writer for hire for college popular business plan writer site ca popular cover letter ghostwriters sites top analysis essay ghostwriters sites for college professional term paper ghostwriting for hire for school definition essay on happiness professional descriptive essay writer websites uk cheap problem solving ghostwriter sites usa best letter ghostwriter websites ca buy physics bibliography professional dissertation proposal proofreading services au top phd analysis essay samples professional cover letter ghostwriting services for masters professional thesis statement editing service gb write my mathematics blog letter proofreading service uk write best expository essay on pokemon go professional critical essay writers service ca professional reflective essay editor sites online type my physics home work professional research proposal ghostwriting services usa school ghostwriter services online write sociology movie review esl persuasive essay proofreading website for school best report writer website us top home work ghostwriters sites for college top biography writing site uk popular creative writing writers service for mba thank god for the atom bomb top homework editor websites ca top personal statement proofreading sites gb pay for my sociology creative writing top blog post editing service au top persuasive essay editing for hire au cheap blog writing service au custom article writer sites for mba top problem solving editing service for phd popular annotated bibliography writing websites usa esl assignment ghostwriter services for school pay to get music curriculum vitae scholarship essay writer sites au popular creative essay proofreading website uk esl movie review proofreading for hire au article ghostwriting service gb custom presentation editing websites for school help me write anthropology business plan custom college resume examples earth science essay ghostwriters for hire write shakespeare studies home work top presentation writers websites us best speech writers service for phd custom course work editing website popular persuasive essay editing service au cheap critical thinking ghostwriters website for university custom letter ghostwriters website for school cheap content writing sites for mba cover letter ghostwriters sites uk custom definition essay ghostwriters services uk cheap article proofreading services ca popular thesis proposal ghostwriting for hire for school help me write best academic essay on hillary clinton professional report proofreading sites for college popular blog writer websites usa cheap creative writing writer site uk best cheap essay ghostwriters website usa esl dissertation results ghostwriting for hire uk popular speech writing site for phd themes for the necklace popular personal statement editor website us best homework writers sites us custom university essay writing websites for phd professional research proposal writing websites for school article ghostwriter sites uk homework editing website us criminal law ghostwriters for hire analysis writing for hire type my management article review popular expository essay ghostwriters website usa cheap resume editing site usa top cheap essay ghostwriter website for phd write me best article review cheap thesis statement editing for hire usa cheap dissertation proposal ghostwriting services for mba best curriculum vitae ghostwriters site professional university personal essay samples popular course work writers websites for masters best dissertation hypothesis writers service uk custom thesis statement writers services gb music essay editing services best dissertation results writer site usa custom ghostwriting sites for college do my culture cover letter custom homework writing services gb write me top expository essay on founding fathers cheap cv proofreading service gb top research paper ghostwriters services usa help with statistics article review pay for custom university essay on shakespeare government writing service best case study ghostwriters services ca help me write government dissertation write my esl scholarship essay on hillary clinton professional letter ghostwriter for hire uk earth science ghostwriters service top bibliography proofreading sites for university professional custom essay ghostwriting website cheap dissertation proposal writing service ca critical essay ghostwriters services usa professional thesis writer services gb themes in robin hood cheap home work proofreading site write custom college essay on hacking cheap critical thinking writing websites au popular admission essay editor for hire for university professional analysis essay editing websites online professional university essay editor services gb custom movie review writer for hire online mark twain and realism popular argumentative essay editor websites au top dissertation results writing website for university custom papers ghostwriting sites for masters help me write esl descriptive essay on hillary esl definition essay writing websites gb esl critical essay writers websites popular thesis proposal ghostwriters site online do my math speech cheap masters essay editor websites for masters the necklace conflict esl letter ghostwriter websites esl persuasive essay writers for hire usa cheap blog post ghostwriting services usa order education thesis professional admission paper editing site online essays ghostwriter services ca professional blog editing service for masters custom best essay ghostwriter sites for phd thesis statement writers service online custom papers writer websites us problem solving proofreading for hire us top cover letter writer service for mba best dissertation methodology ghostwriters website au pay to get science dissertation methodology cheap cover letter editing website for masters top reflective essay proofreading websites for phd cheap essays editor service for mba esl school scholarship essay assistance top dissertation results ghostwriter service for phd type my critical analysis essay on presidential elections cheap cv proofreading sites uk cheap application letter editor site au pay to do political science application letter thesis ghostwriters site usa ideas for cv cheap resume ghostwriters for hire ca best dissertation writers service for school cheap content proofreading service for phd esl article review writing for hire us essay on sportsmanship custom mba essay editor service online popular case study proofreading for hire for college professional course work ghostwriters for hire us cheap content writing sites online write popular movie review custom cheap essay editing site for university help writing cheap rhetorical analysis essay on donald trump custom letter ghostwriter for hire for mba cheap school phd essay samples types of deterrence help writing popular blog post online best cover letter editing services for college esl personal essay writing for hire for university popular college scholarship essay topic custom literature review proofreading site for college best essays ghostwriters websites usa top proofreading site usa cheap course work proofreading websites online esl essays editing site ca professional thesis statement ghostwriter websites popular critical thinking ghostwriter service for masters buy professional university essay on lincoln pay for political science bibliography best college admission paper pay to do cheap rhetorical analysis essay on presidential elections esl article review editor service ca dissertation introduction ghostwriting websites au write my mathematics dissertation abstract top admission paper writers site usa nursing ghostwriter website top writing for hire for university decomposition of copper carbonate help me write top admission essay on hillary type my geology dissertation chapter best course work writer service for college personal essay ghostwriters for hire online professional college essay writers service for mba film bodies gender genre and excess best definition essay writing website for mba popular blog post editor websites gb cheap scholarship essay writer sites for masters how boats work popular college movie review ideas write me esl argumentative essay on usa cheap scholarship essay editor website for phd esl personal statement proofreading for hire for university popular essay ghostwriter website gb popular curriculum vitae writers service for mba popular dissertation methodology writer site for mba help with best dissertation abstract best resume writer service gb esl homework editing website au cheap definition essay writer service for phd esl home work ghostwriter site for university help with my top school essay on civil war popular paper ghostwriting service ca custom dissertation results writers sites gb critical ghostwriter services usa custom dissertation results writer services usa my life philosophy pay for custom expository essay on usa cheap dissertation chapter ghostwriter services for college professional dissertation results ghostwriting site for mba pay to do poetry homework top critical thinking ghostwriting services gb professional curriculum vitae editing websites ca creative writing service usa professional scholarship essay ghostwriter websites usa esl dissertation conclusion proofreading site us essays on war professional homework proofreading service online best cover letter writers sites for college custom cheap essay proofreading site for masters cheap bibliography editing sites usa popular best essay writer site us best blog ghostwriter website ca dissertation proposal ghostwriter services us professional cv ghostwriting websites uk esl personal essay ghostwriters service au cheap curriculum vitae proofreading website for college professional dissertation editing sites gb professional dissertation introduction ghostwriters service for phd best best essay editing service for college freedom in america essay custom dissertation conclusion writing website for mba cheap blog editing site online custom writers website us best problem solving writing website uk custom academic essay writer websites ca cheap application letter writers sites for mba professional phd essay editor website help with sociology dissertation chapter top admission paper proofreading websites uk popular personal essay ghostwriting for hire au best phd dissertation topic pay to write investments literature review dare program essays professional biography ghostwriters website for university professional assignment editor sites for masters cheap paper editing websites for university top reflective essay writer service uk best critical analysis essay editing services for university best research proposal ghostwriting site for school essay writing site uk custom scholarship essay ghostwriter for hire for university ed masry law firm custom thesis statement ghostwriters site us custom admission essay writers services gb esl cheap essay editor service esl blog proofreading for hire gb custom bibliography writer service usa dissertation conclusion editing sites online custom letter writing sites for mba best argumentative essay ghostwriting service popular masters essay ghostwriting services analysis ghostwriter sites usa top book review ghostwriters websites for college esl argumentative essay writing for hire usa best cheap essay ghostwriter sites custom masters dissertation topics pay to do best cheap essay on pokemon go top movie review ghostwriting site uk best descriptive essay writing sites for phd custom resume writing sites for university esl rhetorical analysis essay ghostwriting sites for mba thesis proposal editing site online custom dissertation chapter ghostwriting site gb popular phd essay proofreading website usa cheap literature review editor site online cheap university essay editing for hire au persuasive essay proofreading website ca buy government cv james cone books esl application letter editing sites for phd custom case study ghostwriter website online custom school essay ghostwriter site au curriculum vitae ghostwriter site us custom persuasive essay editing website for university samples for masters essay the 60s miniseries custom paper ghostwriters websites gb best dissertation hypothesis ghostwriters websites us professional dissertation conclusion writers website esl home work writers for hire for mba cheap article review writing websites uk popular academic essay ghostwriting for hire ca top essay writing services for college college ghostwriter for hire uk dissertation introduction writer site usa popular best essay editor service write me best dissertation conclusion custom report writers services usa buy professional critical analysis essay on civil war top admission paper editor site uk custom phd essay writer services for phd esl best essay writing site for school custom university essay proofreading services usa write shakespeare studies cover letter professional thesis ghostwriter for hire ca pay for my education speech top thesis statement ghostwriter services for university pay for custom college essay on hillary esl cover letter proofreading websites for university best phd dissertation introduction examples popular essays writers websites for masters best expository essay editing services us course work editing sites top definition essay editor sites cheap blog post editor sites uk scholarship essay on civil war top term paper ghostwriting services for masters help writing english home work custom mba letter help cheap research proposal writers sites for school cheap definition essay editing websites ca best term paper writers site for college