Monday, January 2, 2012

SQL Server Query to Find First and Last Day of Month, Week,Quarter, Year

----Start and End of Today
SELECT DATEADD(DD,DATEDIFF(DD,0, GETDATE()),0) as [Start Of Day]
,DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE())+1,0)) as [End Of Day]

----Start and End of Week
SELECT DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0) as [Start Of Week]
,DATEADD(s,-1,DATEADD(WK, DATEDIFF(WK,0,GETDATE())+1,0)) as [End Of Week]

---Start and End of Month
SELECT DATEADD(M, DATEDIFF(M,0,GETDATE()),0) as [Start Of Month]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as [End Of Month]

----Start and End of Next Month
SELECT DATEADD(DD,DATEDIFF(DD,0,DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE()))),0) as [Start of Next Month]
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) as [End Of Next Month]

----Start and End of Quarter
SELECT DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()),0) as [Start Of Qaurter]
,DATEADD(s,-1,DATEADD(QQ, DATEDIFF(QQ,0,GETDATE())+1,0)) as [End Of Quarter]

----Start and End of Year
SELECT DATEADD(YY, DATEDIFF(YY,0,GETDATE()),0) as [Start Of Year]
,DATEADD(s,-1,DATEADD(YY, DATEDIFF(YY,0,GETDATE())+1,0)) as [End Of Year]

No comments:

Post a Comment