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.