· 8 years ago · Dec 10, 2017, 08:46 AM
1-- Drop the ref.dates table if it already exists.
2IF OBJECT_ID('ref.dates') IS NOT NULL DROP TABLE ref.dates;
3GO
4
5
6-- Create a temp table with computed values. We will delete this
7-- temp table at the end of this script.
8CREATE TABLE #dates (
9 date DATE NOT NULL,
10 year AS DATEPART(YEAR, date),
11 month AS DATEPART(MONTH, date),
12 day AS DATEPART(DAY, date),
13 day_of_week AS DATEPART(WEEKDAY, date),
14 day_of_year AS DATEPART(DAYOFYEAR, date),
15 week_of_year AS DATEPART(WEEK, date),
16 quarter AS DATEPART(QUARTER, date)
17);
18GO
19
20
21CREATE TABLE ref.dates (
22 [date] DATE NOT NULL,
23 [year] INT NOT NULL,
24 [month] INT NOT NULL,
25 [day] INT NOT NULL,
26 [day_suffix] CHAR(2) NOT NULL,
27 [day_of_week] INT NOT NULL,
28 [day_of_year] INT NOT NULL,
29 [week_of_year] INT NOT NULL,
30 [first_of_month] DATE NOT NULL,
31 [last_of_month] DATE NOT NULL,
32 [first_of_year] DATE NOT NULL,
33 [last_of_year] DATE NOT NULL,
34 [quarter] INT NOT NULL,
35 [quarter_suffix] CHAR(2) NOT NULL,
36 [month_name] VARCHAR(20) NOT NULL,
37 [day_name] VARCHAR(20) NOT NULL,
38 CONSTRAINT [PK_ref.dates] PRIMARY KEY CLUSTERED ([date])
39);
40GO
41
42
43-- year + month + day must be unique.
44CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.dates_year_month_day] ON ref.dates (year, month, day);
45GO
46
47
48-- year + day_of_year must be unique.
49CREATE UNIQUE NONCLUSTERED INDEX [UX_ref.dates_year_day_of_year] ON ref.dates (year, day_of_year);
50GO
51
52
53-- Set the start date and the number of years. Going as far back as 1900, and running for
54-- 300 years ought to cover every possible scenario.
55DECLARE @start_date DATE = CONVERT(DATE, '1900-01-01');
56DECLARE @number_of_years INT = 300;
57
58-- Compute the end date. Add the number of years, then back up one day.
59DECLARE @end_date DATE = DATEADD(YEAR, @number_of_years, @start_date);
60SET @end_date = DATEADD(DAY, -1, @end_date);
61
62-- Insert rows into the temp table.
63WITH cte_date (date) AS
64(
65 SELECT DATEADD(DAY, rownum - 1, @start_date)
66 FROM
67 (
68 SELECT TOP (DATEDIFF(DAY, @start_date, @end_date)) rownum = ROW_NUMBER() OVER (ORDER BY number)
69 FROM ref.numbers
70 ) AS dates
71)
72INSERT INTO #dates (date)
73SELECT date
74FROM cte_date;
75GO
76
77
78INSERT INTO ref.dates (
79 [date],
80 year,
81 month,
82 day,
83 day_suffix,
84 day_of_week,
85 day_of_year,
86 week_of_year,
87 first_of_month,
88 last_of_month,
89 first_of_year,
90 last_of_year,
91 quarter,
92 quarter_suffix,
93 month_name,
94 day_name
95)
96SELECT date,
97 year,
98 month,
99 day,
100 ref.fn_numeric_suffix(day),
101 day_of_week,
102 day_of_year,
103 week_of_year,
104 MIN(date) OVER (PARTITION BY year, month),
105 MAX(date) OVER (PARTITION BY year, month),
106 MIN(date) OVER (PARTITION BY year),
107 MAX(date) OVER (PARTITION BY year),
108 quarter,
109 ref.fn_numeric_suffix(quarter),
110 DATENAME(MONTH, date),
111 DATENAME(WEEKDAY, date)
112FROM #dates
113ORDER BY date ASC;
114GO
115
116
117-- Drop the temporary table. We no longer need it.
118DROP TABLE #dates;
119GO
120
121
122SELECT * FROM ref.dates;
123GO