June 8, 2011

Some useful SQL Queries on Date functions

TO Calculate first day of month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

First Day of Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

Last day of Prior month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Last day of current month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))