Tuesday, December 6, 2011

How to calculate dynamic dates in SQL Server

Quite often we need to calculate some kind of dynamic date based on the current date or the user's selection in a date filter. Examples include "First day of the year", "First day of the month" and so on.

To help you build such date formulas I have assembled a set of useful calculations for you to use.

Note: To use the calculations in Filter Modifiers you simply replace "getdate()" with the desired filter placeholder, e.g. "{Value1}".

First Day of Month

DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Monday of the Current Week

DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

First Day of the Year

DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

First Day of the Quarter
DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

Midnight for the Current Day
DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

Last Day of Prior Month
DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

Last Day of Prior Year
DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Last Day of Current Month
DATEADD(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

Last Day of Current Year
DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))

First Monday of the Month
DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

First Day of the Month 12 Months Ago
DATEADD(mm, DATEDIFF(mm,0,getdate()) - 12, 0)
Note: You can change "12" to any number of months ago you wish.