Thursday, June 01, 2006

Use DATEADD And DATEDIFF To Get The Start And End Date For A Quarter

In the microsoft.public.sqlserver.programming forum a person asked how to get the first day of the current quarter. My answer was this: SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
So I decided to expand on that here

--Start date of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)

--start and end dates of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+1, -1) AS LastDayOfQuarter


--start day of the quarter for 20060501
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060501')+0, 0)

--start and end dates of the quarter for 20060501
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060501')+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, '20060501')+1, -1) AS LastDayOfQuarter


--start and end dates of the quarter for 20060201
SELECT DATEADD(qq, DATEDIFF(qq, 0, '20060201')+0, 0) AS FirstDayOfQuarter,
DATEADD(qq, DATEDIFF(qq, 0, '20060201')+1, -1) AS LastDayOfQuarter

No comments: