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.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply