First Day of Current Month – DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0)
Last Day of Current Month – DATEADD(dd,-1,DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0)))
First Day of Next Month – DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0))
Last Day of Previous Month – DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0, @DateParameter), 0))
First Day of Current Quarter – DATEADD(qq, DATEDIFF(qq,0, @DateParameter), 0)
Last Day of Current Quarter – DATEADD(dd,-1,DATEADD(qq,1,DATEADD(qq, DATEDIFF(qq,0, @DateParameter), 0)))
Last Day of Previous Quarter – DATEADD(dd,-1,DATEADD(qq, DATEDIFF(qq,0, @DateParameter), 0))
First Day of Current Year – DATEADD(yy, DATEDIFF(yy,0, @DateParameter), 0)
First Day of Previous Year – DATEADD(yy, DATEDIFF(yy, 0,DATEADD(yy, -1, @DateParameter)), 0)
Last Day of Previous Year – DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0, @DateParameter), 0))
This Day Last Year – DATEADD(yy, -1, @DateParameter)
When writing any date-driven reports, you will certainly come across the challenge of finding the correct functions for defaulting dates. Below are some common date functions you may need for queries in your SQL Server Reporting Services (SSRS) reports.
NOTE: If you are using these by themselves in Management Studio to test, use “select” in front of the line and either declare your parameter or switch out the @DateParameter with a test date (ex. ‘8/18/2012’). Otherwise, embed them as a part of the query (ex. Where docdate > x) with x being the statement below.