Skip to content

Instantly share code, notes, and snippets.

@jrotello
Created January 24, 2017 00:18
Show Gist options
  • Save jrotello/b4e817109577918c2211cd2da9cce4fc to your computer and use it in GitHub Desktop.
Save jrotello/b4e817109577918c2211cd2da9cce4fc to your computer and use it in GitHub Desktop.
TSQL to Generate a date table
DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 100;
-- prevent set or regional settings from interfering with
-- interpretation of dates / literals
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
-- this is just a holding table for intermediate calculations:
CREATE TABLE #dim
(
[date] DATE PRIMARY KEY --,
--[day] AS DATEPART(DAY, [date]),
--[month] AS DATEPART(MONTH, [date]),
--FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
--[MonthName] AS DATENAME(MONTH, [date]),
--[week] AS DATEPART(WEEK, [date]),
--[ISOweek] AS DATEPART(ISO_WEEK, [date]),
--[DayOfWeek] AS DATEPART(WEEKDAY, [date]),
--[quarter] AS DATEPART(QUARTER, [date]),
--[year] AS DATEPART(YEAR, [date]),
--FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
--Style112 AS CONVERT(CHAR(8), [date], 112),
--Style101 AS CONVERT(CHAR(10), [date], 101)
);
-- use the catalog views to generate as many rows as we need
INSERT #dim([date])
SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
-- on my system this would support > 5 million days
ORDER BY s1.[object_id]
) AS x
) AS y;
SELECT * FROM #dim
DROP TABLE #dim
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment