Keith Rull

Here are some useful T-SQL code snippets that you can uses for manipulating date.

--calculates the first day of the month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

--calculates the first day of the year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

--calculates the first day of the quater
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

--calculates the first monday of the month
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

--calculates the last day of the prior month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

--calculates the last day of the prior year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--calculates the last day of the current year
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

--calculates the monday of the current week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

--calculates the yesterdays date
select DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)