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 your queries.

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.

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)