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.

DATECODERESULT 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)) 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 (2 – DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))>=0 then dateadd(wk, 3,(dateadd(m, datediff(m, 0, getdate()), 0)) + (2 – DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) else dateadd(wk, 4,(dateadd(m, datediff(m, 0, getdate()), 0)) (2 –DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) end04/28/2014
Second Thursday 
of current month
select case when (5 – DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))>=0 then dateadd(wk, 1,(dateadd(m, datediff(m, 0, getdate()), 0)) + (5 – DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) else dateadd(wk, 2, (dateadd(m, datediff(m, 0, getdate()), 0)) + (5 – DATEPART(dw, (dateadd(m, datediff(m, 0, getdate()), 0))))) end04/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
Number of days
in current month
select datediff(day, 
dateadd(day, 1 –
day(getdate()), 
getdate()), 
dateadd(month, 1, 
dateadd(day, 1 –

day(getdate()), 
getdate())))
30

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.

Sometimes when the code above is copied into SQL the minuses don’t copy properly – you can just type over them in SSMS with a “-” and the code will work. I have tried to fix this numerous times, but it inevitably comes creeping back. Sorry about that.  

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: