Finding Specific Dates in SQL Server

I gathered some useful date functions around the net. This is will be helpful in finding specific dates. Here are some of them:

declare @BaseDate datetime

set @BaseDate='20120205 21:30:27.380'

SELECT
Today=isnull(@BaseDate,GETDATE()),

DayToday=datename(dw,isnull(@BaseDate,GETDATE())),

TodayMidnight=DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0),

TodayEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,+1,isnull(@BaseDate,GETDATE()))),0)),


Yesterday=DATEADD(d,-1,isnull(@BaseDate,GETDATE())),

YesterdayMidnight=DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,-1,isnull(@BaseDate,GETDATE()))),0),

YesterdayEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),


Tomorrow=DateAdd(dd,1,isnull(@BaseDate,GETDATE())),

TomorrowMidnight=DATEADD(dd,DATEDIFF(dd,0,DateAdd(dd,1,isnull(@BaseDate,GETDATE()))),0),

TomorrowEndofDay=dateadd(ms,-2,DATEADD(dd,DATEDIFF(dd,0,DATEADD(d,+1,DateAdd(dd,1,isnull(@BaseDate,GETDATE())))),0)),


FirstDayofWeek=dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofWeek=dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))),

FirstDayofLastWeek=dateadd(dd,-7,dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0))),

LastDayofLastWeek=dateadd(dd,-7,dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)),DATEADD(dd,DATEDIFF(dd,0,isnull(@BaseDate,GETDATE())),0)))),


FirstDayofMonth=DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0),

LastDayofMonth=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE()))+1,0))),

FirstDayofLastMonth=DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofLastMonth=DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,isnull(@BaseDate,GETDATE())),0))),


LastDayLastQuarter=DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())), 0)),

FirstDayThisQuarter=DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())), 0),

LastDayThisQuarter=DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) +1, 0)),

FirstdayNextQuarter=DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) + 1, 0),

LastDayNextQuarter= DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, isnull(@BaseDate,GETDATE())) +2, 0)),


FirstDayofYear=DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0),

LastDayofYear=DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE()))+1,0))),

FirstDayofLastYear=DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0)),

LastDayofLastYear=DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,isnull(@BaseDate,GETDATE())),0)))

Here's the result set:

Today                   DayToday    TodayMidnight           TodayEndofDay           
----------------------- ----------- ----------------------- ----------------------- 
2012-02-05 21:30:27.380 Sunday      2012-02-05 00:00:00.000 2012-02-05 23:59:59.997 


Yesterday               YesterdayMidnight       YesterdayEndofDay       
----------------------- ----------------------- ----------------------- 
2012-02-04 21:30:27.380 2012-02-04 00:00:00.000 2012-02-04 23:59:59.997 


Tomorrow                TomorrowMidnight         TomorrowEndofDay        
----------------------- ----------------------- ----------------------- 
2012-02-06 21:30:27.380 2012-02-06 00:00:00.000 2012-02-06 23:59:59.997 


FirstDayofWeek          LastDayofWeek           
----------------------- ----------------------- 
2012-01-30 00:00:00.000 2012-02-05 23:59:59.997 


FirstDayofLastWeek      LastDayofLastWeek       
----------------------- ----------------------- 
2012-01-23 00:00:00.000 2012-01-29 23:59:59.997 


FirstDayofMonth         LastDayofMonth          
----------------------- ----------------------- 
2012-02-01 00:00:00.000 2012-02-29 23:59:59.997 


FirstDayofLastMonth     LastDayofLastMonth      
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-01-31 23:59:59.997 


FirstDayLastQuarter     LastDayLastQuarter
----------------------- -----------------------
2011-10-01 00:00:00.000 2011-12-31 00:00:00.000


FirstDayThisQuarter     LastDayThisQuarter
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-03-31 00:00:00.000


FirstdayNextQuarter     LastDayNextQuarter
----------------------- -----------------------
2012-04-01 00:00:00.000 2012-06-30 00:00:00.000


FirstDayofYear          LastDayofYear           
----------------------- ----------------------- 
2012-01-01 00:00:00.000 2012-12-31 23:59:59.997 


FirstDayofLastYear      LastDayofLastYear
----------------------- -----------------------
2011-01-01 00:00:00.000 2011-12-31 23:59:59.997

Remember, the variable needs to be of DateTime datatype, not SmallDateTime.

~~ CK