Quarter Start and End Dates Table in SQL Server 2008 with DATEDIFF and DATEADD

I was asked if I could populate a table with the next 40 quarter end dates. It's always best to populate calendar tables than to try to write SQL code to figure it out on the fly or in a view. That way lies madness.

Without further ado, this populated the next 100 dates.

/*
* *** Create Simple Quarters Table ***
* Just hitting execute will create a table with Quarter Start and End dates
* and a row number to order them by or do joins with later.
*/

-- If you want more than 100 future quarters, watch out for max recursion. Scroll down and bump that number accordingly.

-- Uncomment this after you've made the Quarters table
DROP TABLE [Quarters]

DECLARE @todaysDate [datetime];
DECLARE @todaysQtrNum int;
DECLARE @qtrCalc int

SET @todaysDate = GETDATE(); -- Or set the date yourself here
SET @todaysQtrNum = DATEDIFF(q, 0, @todaysDate);
SET @qtrCalc = 99; -- How many future quarters are we calculating (1 more will be added for current)

WITH Numbers(n)
AS
(
SELECT 0 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < @qtrCalc
)

SELECT n as QtrNumber,
--@todaysDate as Today,
--@todaysqtrnum as TodaysQtrNumber,
DATEADD(q, @todaysQtrNum+n, 0) AS QuarterStart,
DATEADD(d, -1, DATEADD(q, @todaysQtrNum+n+1, 0)) AS QuarterEnd
INTO [Quarters]
from Numbers
--OPTION(MAXRECURSION 100) -- defaults 100

-- See your creation
SELECT * FROM [Quarters]

P.S. Certainly this could be incorporated into a larger Calendar table. It just wasn't needed here and sometimes you might want the specialization.

P.P.S. I found the numbers query technique using recursion on Code.I.net blog. It won't get you past around 10000 for quarters and AFAIK recursion is limited to 32767 anyways. But it worked and sometimes that's all you need (especially when it's one time use).