Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Some Common Date Routines

I'm sure that these routines are available in a variety of locations, this just adds one more place that people can find them if they need them.  These are some common routines for generating commonly needed dates.  I hope people find them useful in MS SQL Server.

 

declare @ThisDate datetime;
set @ThisDate = getdate();

select dateadd(dd, datediff(dd, 0, @ThisDate), 0)     -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0)     -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0)     -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0)     -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0)     -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year

Comments

Posted by ndtownsend on 17 September 2009

Can you explain what function the zero have in the following examples?

dateadd(m, datediff(m, 0, getdate()), -1)

dateadd(mm, datediff(mm, 0, getdate()) - 1, 0)

Posted by Lynn Pettis on 17 September 2009

That is easy, the 0 (zero) represents the "zero" data, 1900-01-01.  In each of the statements above, you could replace the 0 with '19000101' and they will work the same.

Posted by Tom Garth on 6 January 2010

Lynn,

I just thought I should check out the "musings" of someone who had logged over 10,000 visits to SSC, because it's an impressive number made even more so knowing you only started less than 5 years ago.

I've written most of these routines on demand when I needed them, but never saved them because I've never had them all in one place before. Their in my toolbox now.

Thanks

Posted by Jason Brimhall on 15 January 2010

Nice Listing Lynn.  This is useful.

Posted by skailey on 19 January 2010

Very helpful for me today, Lynn.  Thank you!

Posted by john.arnott on 21 January 2010

Thanks, Lynn.  Nice to have this laid out for easy use.  

It did get me looking at how the "week" (wk) calc's work.  Looks as though they resolve to Monday as the first day of the week only because 1/1/1900 was a Monday.  Thus, that result is entirely independant of the DATEFIRST settting, which defaults to Sunday being the first day for US-English installations.

One way to find the boundary days for weeks would be to use the DatePart function to find the day of the week for the date in question, then determine the date for the end of the previous week with modulo 7. From there, add 1 for the beginning of this week, 8 for the beginning of next week or -6 for the start of the previous week.  This has the bonus advantage of working with alternative DateFirst settings.

SET DATEFIRST 7

Declare @ThisDate datetime

Set @ThisDate = getdate()

Select @thisdate thisdate

, DateAdd(dd, 0,Datediff(dd, 0, @thisDate)) - (datepart(dw,@thisDate) %7)    -- Most Recent end of week date (Saturday)

, DateAdd(dd, 0,Datediff(dd, 0, @thisDate)) - (datepart(dw,@thisDate) %7) +1 -- Beginning of this week

, DateAdd(dd, 0,Datediff(dd, 0, @thisDate)) - (datepart(dw,@thisDate) %7) +8 -- Beginning of this week

, DateAdd(dd, 0,Datediff(dd, 0, @thisDate)) - (datepart(dw,@thisDate) %7) -6 -- Beginning of previous week

Posted by brett.y on 27 December 2010

Just got directed here and this answered my question brilliantly, thanks Lynn.

Posted by Lynn Pettis on 27 December 2010

I'm glad this blog post was helpful in solving your problem.

Posted by Kila on 5 August 2011

Would you have anything for calculating the 1st and last day of the Month before Last (such as for June, since we are now in August)  Thanks!

Posted by Jeff Moden on 28 March 2012

I know it's an old post, Kila, but have a look at the "beginning" formulas and the month level and simply figure out if you need to substract something instead of adding something.

Posted by Docktur Todd on 13 May 2012

Lynn,

Thank you so very much for the explanation and reason behind the variables.  Please keep them coming.  This are ever so useful.

Posted by Eric L Hackett on 15 May 2012

Can anyone tell me why this doesn't work for HOUR?

Posted by capn.hector on 16 May 2012

SELECT DATEADD(HH,DATEDIFF(HH,0,GETDATE()),0) works for me to get just the hour.  if you go down to the Second the number gets to big and you get an arithmetic overflow

Posted by Mark Fitzgerald-331224 on 17 May 2012

@capn.hector : you would need to move the start point close to the @thisDate, for instance the current day. (if you go down to microseconds then you will need to go to nearest second).  This is due to datediff return type being int.

Possible solutions:

Seconds :-

DATEADD(s,datediff(s,DATEADD(d,datediff(d,0,@d),0),@d),DATEADD(d,datediff(d,0,@d),0))

Milliseconds :-

DATEADD(ms,datediff(ms,DATEADD(mi,datediff(mi,0,@d),0),@d),convert(datetime2,DATEADD(mi,datediff(mi,0,@d),0)))

Microseconds :-

DATEADD(mcs,datediff(mcs,DATEADD(mi,datediff(mi,0,@d),0),@d),convert(datetime2,DATEADD(mi,datediff(mi,0,@d),0)))

Or you could always convert back and forth and truncate to get the same answer.

Seconds :-

select convert(datetime2,left(CONVERT(varchar(30),@d,126),19))

Milliseconds :-

select convert(datetime2,left(CONVERT(varchar(30),@d,126),23))

Microseconds :-

select convert(datetime2,left(CONVERT(varchar(30),@d,126),26))

Posted by jarid.lawson on 18 December 2012

This is enlightening. I came up with my own version of this earlier today, and was promptly schooled by many on the site already. I've add your list to my 'All Things SQL' SpringBoard for quick review as I need it. Thank you for adding it.

Leave a Comment

Please register or log in to leave a comment.