Posts

Showing posts from September, 2010

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 ) AS BEGIN 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 (