September 13, 2010 
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)) | 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))))) end | 04/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))))) 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 |
| 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 @@datefirstIf 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.
Victoria Yudin 
Exactly what I was looking for! Thank you very much. Cheers.
LikeLike
What a truly helpful list! Thank you so much for sharing your work. 🙂
LikeLike