A while back I had a problem crop up where I needed to create a calendar but could only do this in a view. At first I thought, sure no problem, just wheel out the old recursive CTE (common table expression) pattern with some dates like so:-
;WITH cteDates AS (
SELECT
CAST('1900-01-01' AS DATE) AS DateValue
UNION ALL
SELECT
DATEADD(DAY, 1, DateValue)
FROM
cteDates
WHERE
DateValue <= GETDATE()
)
SELECT
DateValue
FROM
cteDates OPTION (MAXRECURSION 32767) -- limit is 32767!
Note that I’m starting at 1st January 1900 (using the ISO 8601 date format of ‘YYYY-MM-DD‘ – see more about T-SQL dates here) as this calendar needs to link to birth dates. Then we finish at today’s date of course.
There is one small problem however, the limit for recursion when using a CTE in SQL Server is 32767. If you run this query you’ll soon see the error message:-
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.
The CTE solution gets us up to ‘1989-09-18’ (18th September 1989), which would be OK if we were still in the eighties. Of course we could use a much later start date and never hit the limit, but if you need to start at 1st January 1900 and go up to the year 2019 then it IS a problem.
How do we get around this?
In a stored procedure this is not a problem, a simple loop would do the trick. However, remember that we need to write our code as a view so we’re limited by a number of factors. Mainly the fact that we can’t use variables. Essentially we need another method to dynamically generate rows, enter the T-SQL VALUES function which should be familiar to all SQL developers.
Err… OK, how does that solve the problem??
First of all (if you didn’t know already) we can use the VALUES function to generate rows of data. However, writing thousands of VALUES statements is obviously not going to by dynamic or practical. We must break it down a little further… What we can do is break a date into parts, so:-
So if we can generate years, months and days for all the dates we need that might just work. Something like this, but of course we’d still need over 100 values for all the years, in the example I’ve stopped at 1909 but I’m sure you get the idea:-
SELECT
YearValue
FROM (
VALUES
(1900), (1901), (1902), (1903), (1904), (1905), (1906), (1907), (1908), (1909)
) v(YearValue)
So, we must break down years into something smaller and more manageable. Remember primary school (or elementary school for those folk outside of Britain) when you learned about units, tens, hundreds and thousands?
Oh please no, not maths from school…!
SELECT
Thousands.MilleniumValue
,Hundreds.CenturyValue
,Tens.DecadeValue
,Units.YearValue
,TheYear = Thousands.MilleniumValue
+ Hundreds.CenturyValue
+ Tens.DecadeValue
+ Units.YearValue
FROM (
VALUES
(1000), (2000)
) Thousands(MilleniumValue)
CROSS JOIN (
VALUES
(0), (100), (200), (300), (400), (500), (600), (700), (800), (900)
) Hundreds(CenturyValue)
CROSS JOIN (
VALUES
(0), (10), (20), (30), (40), (50), (60), (70), (80), (90)
) Tens(DecadeValue)
CROSS JOIN (
VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
) Units(YearValue)
ORDER BY
TheYear DESC
Right, things are starting to get interesting. The above script will generate a year sequence using a combination of the units, tens, hundreds and thousands values. Note the CROSS JOIN‘s which join all values to all other values, so for each ‘unit’ we get all the values for each ‘ten’, ‘hundred’ and ‘thousand’ value and so on… You have noticed that this will give us year values from 1000 to 2999 which is a little more than we want, but we can filter those unwanted years out later as you’ll see. If we apply the same logic for months and days we end up with:-
SELECT
TheYear = Thousands.MilleniumValue
+ Hundreds.CenturyValue
+ Tens.DecadeValue
+ Units.YearValue
,MonthOfTheYear.MonthValue
,DayOfTheMonth.DayValue
FROM (
VALUES
(1000), (2000)
) Thousands(MilleniumValue)
CROSS JOIN (
VALUES
(0), (100), (200), (300), (400), (500), (600), (700), (800), (900)
) Hundreds(CenturyValue)
CROSS JOIN (
VALUES
(0), (10), (20), (30), (40), (50), (60), (70), (80), (90)
) Tens(DecadeValue)
CROSS JOIN (
VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
) Units(YearValue)
-- 12 months in a year
CROSS JOIN (
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
) MonthOfTheYear(MonthValue)
-- max of 31 days in any month
CROSS JOIN (
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
,(11), (12), (13), (14), (15), (16), (17), (18), (19), (20)
,(21), (22), (23), (24), (25), (26), (27), (28), (29), (30)
,(31)
) DayOfTheMonth(DayValue)
ORDER BY
TheYear DESC
Now we can put it all together and get a reasonably fast and simple query to generate date values which we use as the basis for a simple calendar view:-
SELECT
DateValue
,DateNumber
,YearValue
,MonthValue
,DayValue
FROM (
SELECT
CAST(CAST(rd.DateNumber AS VARCHAR(8)) AS DATE) AS DateValue
,rd.DateNumber
,rd.YearValue
,rd.MonthValue
,rd.DayValue
FROM (
SELECT
-- convert our values into an integer e.g. 19000101
-- we can use this to convert to a date later and/or a calendar key value
((((Thousands.MilleniumValue + Hundreds.CenturyValue + Tens.DecadeValue + Units.YearValue) * 100) + MonthOfTheYear.MonthValue) * 100) + DayOfTheMonth.DayValue AS DateNumber
-- add the units, tens, hundreds and thousands to get our year
,Thousands.MilleniumValue + Hundreds.CenturyValue + Tens.DecadeValue + Units.YearValue AS YearValue
-- month and day values
,MonthOfTheYear.MonthValue
,DayOfTheMonth.DayValue
FROM (
VALUES
(1000), (2000)
) Thousands(MilleniumValue)
CROSS JOIN (
VALUES
(0), (100), (200), (300), (400), (500), (600), (700), (800), (900)
) Hundreds(CenturyValue)
CROSS JOIN (
VALUES
(0), (10), (20), (30), (40), (50), (60), (70), (80), (90)
) Tens(DecadeValue)
CROSS JOIN (
VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
) Units(YearValue)
-- 12 months in a year
CROSS JOIN (
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
) MonthOfTheYear(MonthValue)
-- max of 31 days in any month
CROSS JOIN (
VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
,(11), (12), (13), (14), (15), (16), (17), (18), (19), (20)
,(21), (22), (23), (24), (25), (26), (27), (28), (29), (30)
,(31)
) DayOfTheMonth(DayValue)
WHERE
-- filter out date values we don't need
((((Thousands.MilleniumValue + Hundreds.CenturyValue + Tens.DecadeValue + Units.YearValue) * 100) + MonthOfTheYear.MonthValue) * 100) + DayOfTheMonth.DayValue
BETWEEN 19000101 AND CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
) rd
WHERE
-- only get values where its a valid date!
ISDATE(CAST(rd.DateNumber AS VARCHAR(8))) = 1
) d
ORDER BY
d.DateValue DESC
There we go, the basis of a simple calendar but in a view, no table or stored procedure required. The only thing to do now is add more fields for different aspects of the date e.g. fiscal year, fiscal month of the year etc…
P.S. remember to remove the ORDER BY clause if you do make this into a view, either that or change the SELECT into a SELECT TOP (100) PERCENT WITH TIES
