SQL 2012 – Generate Calendar with Cross joins

“cross join” is a difficult to explain to trainees.  The mechanics aren’t very complicated.  However, the question is where you would ever use them?

One example that I came up with is generating a calendar.  Let’s say I want to generate a list of dates in a set of years.  For example, I want a query that returns every valid date in the years between 2001 and 2013.  I don’t want complicated logic to deal with whether a month has 30 days or 31 days or even 28 days. I don’t want to add any complicated logic that determines if the year is a leap year.

The following script uses common table expressions (CTE) to generate three virtual tables (years, months, days).  I use the recursive features of CTEs to create a range of values. The years range from the values set in the variables.  The months range from 1 to 12 and days from 1 to 31.  I then combine the years, months and days in a final CTE that I call datekeys.  That CTE cross joins all of the other three CTEs (years, months, days) and then concatenates the values into an ISO date.  Finally, I use the new function for 2012 TRY_CONVERT to convert the datekey (which has been cast to varchar). If the TRY_CONVERT doesn’t return NULL then the date is valid.

declare @startYear int = 2001;
declare @endYear int = 2012;

with years as (Select @startYear as [Year]
union all
Select [Year] + 1
from years 
where [Year] <  @endYear)
, months as (Select 1 as [Month] 
union all 
Select [Month] + 1
from months 
where [Month] < 12)
, days as (
select 1 as [Day]
union all
select [Day] + 1
from days
where [Day] < 31
)
, datekeys as (
select
[year] * 10000 + [month] * 100 + [day]
 as datekey
from years cross join months cross join days
)
select 
datekey,
try_convert(date, cast(datekey as varchar(8))) as DateValue
 from datekeys
 where try_convert(date, cast(datekey as varchar(8))) is not null;

I can’t say whether this is the best performing way to do this, but it quickly generated a date list on my box.

Leave a Reply

%d