An infinite date table

Here is the creation script for a neat function that will generate a table full of every date in the given range.

-- =============================================
-- Author:  Aaron D. Wells
-- Create date: 9/23/2010
-- Description: Get a list of dates in a range
-- =============================================
CREATE FUNCTION fn_Dates (@fromDate date, @toDate date)
RETURNS @dateTable TABLE( [Date] Date NOT NULL, FiscalYear int NOT NULL, FiscalMonth int NOT NULL )
     WITH CTE_DatesTable([date])
     AS (
     SELECT @fromDate AS [date]
          UNION ALL
          SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable
          WHERE DATEADD(dd, 1, [date]) <= @toDate)  
          INSERT @dateTable
               SELECT [date],
                    CASE WHEN MONTH(date) > 9 THEN YEAR(date) + 1 ELSE YEAR(date) END FiscalYear,
                    CASE WHEN MONTH(date) > 9 THEN MONTH(date) - 9 ELSE MONTH(date) + 3 END FiscalMonth
               FROM CTE_DatesTable OPTION (MAXRECURSION 0)
Using the function is pretty simple, just give it a starting and ending date. By the way, the FiscalYear column is used to calculate the fiscal year for the company I was working with when i wrote this. Your mileage may vary.


Popular posts from this blog

KnockoutJS, WebAPI, and TypeScript

Database Projects, SQL Unit Tests, and TeamCity