Date Range in SQL

While creating SQL queries for reports, it’s usually required to get a continuous list of dates for a given period. E.g.: get all dates from 1980 Jan 01 till 2013 Dec 31 (12419 records). In this post I would like to describe several ways how this can be done and pros and cons of these methods.

First I will briefly describe each method and then I will move them all into one table to make some conclusions.

Use system tables (or large tables in your database)

The query looks like:

DECLARE @startDate datetime, @endDate datetime;SET @startDate = {d N'1980-01-01'};
SET @endDate = {d N'2013-12-31'};
select DATEADD(d, n, @startDate)
from (
 select 0 n
 union all
 select row_number() over (order by a.ID) n
 from sysobjects a with(nolock) 
 cross join sysobjects b with(nolock)
) tab1
group by n
having DATEADD(d, n, @startDate) <= @endDate
order by n 

Advantages:

• Small amount of code.
• Great performance: CPU 63, Reads 1500, Duration 0.09 seconds.

Disadvantages:

• We are working with system tables and Microsoft can change their names or structure in future versions of SQL Server.
• Depend on table size – for small databases this method cannot be used as there would be too little records in sysobjects table.

Use large union query

DECLARE @startDate datetime, @endDate datetime;
SET @startDate = {d N'1980-01-01'};
SET @endDate = {d N'2013-12-31'};
with 
t0(n) as 
( 
 select 1 
 union all 
 select 1 
), 
t1(n) as 
( 
 select 1 
 from t0 as a
 cross join t0 as b 
), 
t2(n) as 
( 
 select 1 
 from t1 as a
 cross join t1 as b 
), 
t3(n) as 
( 
 select 1 
 from t2 as a
 cross join t2 as b 
), 
t4(n) as 
( 
 select 1 
 from t3 as a
 cross join t3 as b 
), 
t5(n) as 
( 
 select 1 
 from t4 as a
 cross join t4 as b 
), 
Numbers(n) as 
( 
 select row_number() over (order by n) as n 
 from t5 
)
select dateadd(d, n - 1, @startDate) as n 
from Numbers 
where n <= datediff(d, @startDate, @endDate) + 1 

Advantages:

• Ideal performance: CPU – 16, Reads – 0, Duration – 0,009 seconds.

Disadvanteges:

• Code is rather bulky.

Use CTE

DECLARE @startDate datetime, @endDate datetime;
 
SET @startDate = {d N'1980-01-01'};
SET @endDate   = {d N'2013-12-31'};
 
WITH [dates] ([Sequence], [date]) AS
   (SELECT 1 AS [Sequence]
          ,@startDate AS [date]
    UNION ALL
    SELECT Sequence + 1 AS Sequence
          ,DATEADD(d, 1, [date]) AS [date]
    FROM [dates]
    WHERE [date] < @endDate)
 
SELECT [Sequence]
      ,[date]
FROM [dates]
OPTION (MAXRECURSION 32747); 

Advantages:

• Code is ok – it’s rather compact.

Disadvantages:

• Performance is rather poor, especially for number of reads: CPU – 172, Reads – 111000, Duration – 0,3 seconds.
• We cannot use it on SQL Server 2000.
• You are limited with 89 years – but I think for most cases this will be enough.

To sum up, the small table with all three methods in one place, with some my thoughts about each of them.

Method	Advantages	Disadvantages	Conclusion
System Tables	Small Code Good performance	Depends on internal  SQL objectsDepends on size of the database	From my point of view not reliable method. The performance lead is not so much higher than the others methods.
Large union queries	The best performance	Code not very good	Great method. It  is flexible enough to feed needs for any date ranges and works very quickly.
CTE	Small code	Poor performance (especially for reads).	Can be used for not large date ranges (it shows quite good performance for 1-2 years period). But in general case the second method is much better.