Coding specific dates in SQL Server


Quite often when writing reports I need to write a formula to calculate dates. Dates are not very straightforward to code in SQL Server, and I have started keeping a list of various formulas so that I do not have to re-write the code every time. I have previously posted some code on calculating date differences in SQL, however below is some code for getting specific dates.

DATE CODE RESULT ON 04/05/2014
Current date (and time) select getdate() 04/05/2014
First day of current month select dateadd(m, datediff(m, 0, getdate()), 0) 04/01/2014
Last day of current month select dateadd(ms, 3, dateadd(m, 0, dateadd(m, datediff(m, 0, getdate())+1, 0))) 04/30/2014
First day of previous month select dateadd(m, 1, dateadd(m, datediff(m, 0, getdate()), 0)) 03/01/2014
Last day of  previous month select dateadd(d, 1, dateadd(m, datediff(m, 0, getdate()), 0))

or

select dateadd(day, –datepart(day,(getdate())), getdate())

03/31/2014
First day of  next month select dateadd(m, 1, dateadd(m, datediff(m, 0, getdate()), 0)) 05/01/2014
Last day of  next month select dateadd(d, 1, dateadd(m, datediff(m, 0, dateadd(m, 2, getdate())), 0)) 05/31/2014
Last day of month 2  months ago select dateadd(d, 1, dateadd(m, datediff(m, 0, dateadd(m,1, getdate())), 0)) 02/28/2014
Last day of previous month in the previous year select dateadd(d, 1, dateadd(m, datediff(m, 0, dateadd(m, 12, getdate())), 0)) 03/31/2013
Monday of current week select dateadd(wk, datediff(wk, 0, getdate()), 0) 03/31/2014
Sunday of current week select dateadd(wk, datediff(wk, 0, getdate()), 6) 04/06/2014
Fourth Monday of current month select case when (2DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))>=0 then dateadd(wk, 3,(dateadd(m, datediff(m, 0, getdate()), 0)) + (2DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) else dateadd(wk, 4,(dateadd(m, datediff(m, 0, getdate()), 0)) (2DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) end 04/28/2014
Second Thursday of current month select case when (5DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))>=0 then dateadd(wk, 1,(dateadd(m, datediff(m, 0, getdate()), 0)) + (5DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) else dateadd(wk, 2, (dateadd(m, datediff(m, 0, getdate()), 0)) + (5DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) end 04/10/2014
First day of current year select dateadd(yy, datediff(yy, 0, getdate()), 0) 01/01/2014
Last day of current year select dateadd(ms, 3, dateadd(yy, 0, dateadd(yy, datediff(yy, 0, getdate())+1, 0))) 12/31/2014
First day of previous year select dateadd(yy, 1, dateadd(yy, datediff(yy, 0, getdate()), 0)) 01/01/2013
Last day of previous year select dateadd(ms, 3, dateadd(yy, 0, dateadd(yy, datediff(yy, 0, getdate()), 0))) 12/31/2013
First day of September in the current year select dateadd(m, 8, dateadd(yy, datediff(yy, 0, getdate()), 0)) 09/01/2014
First day of September in the previous year select dateadd(m, 8, dateadd(yy, datediff(yy, 0, dateadd(yy, 1, getdate())), 0)) 09/01/2013

A few notes:

  • All of the code above uses GETDATE(), or the current date, as a starting point. To test these, just copy what’s in the CODE column above into a SQL query and execute it. If you need to use a parameter instead, you can replace GETDATE() with your parameter name.
  • Be careful with the calculations for weeks. The examples above are assuming that the first day of the week is Sunday. You can check whether this is the same on your SQL Server by running the following query:
       select @@datefirst
    If it returns 7, then your first day of the week is Sunday.
  • Don’t forget to test. While I have tested all the code above, I usually test with the current date and a few other dates in the surrounding months or years. Always test other people’s code before you rely on it.

Thank you to Pinal Dave for some of this information.

2 Responses to “Coding specific dates in SQL Server”

  1. What a truly helpful list! Thank you so much for sharing your work. 🙂

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net - September 14, 2010

    Coding specific dates in SQL Server…

    Victoria Yudin has a great look at Coding specific dates in SQL Server . If you’ve ever struggled with…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: