Thursday, March 09, 2006

useful sql server t-sql date scripts

got code here

First Day of Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

First Day of the 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))

First Monday of the Month
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)

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