How to generate a date range in SQL

There are many situations where you want a continuous date range in your SQL queries.

Image Reference: drupa

One example could be a time series chart that plot your activities on daily basis.

if you simply compose the following query

select date, count(1) from activities group by date order by date

obviously you will get no records on dates with no contributions.

while what needed is to get a range of dates with 0s on dates with no contributions.

There are many approaches to achieve that depending on your database engine, we will cover in this article MySql, Postgres and Sql Server.

Option 1: Create a table that holds long number sequence

The first approach is to create a long table with approx 1500 records (which can generate a date range for 3 years) off course you can adjust the number as you want, but in most cases you will not need more than one year so even 500 records are sufficient.

the table should hold only a primary key of type integer.

create table range(int primary key)

note that we have created one column of type integer, this will not take a lot of storage space, and yes we didn’t created a date column, the conversion will be done on the fly.

So to generate a date range between 2018–01–01 and 2018–12–31 in SqlServer

select dateadd(day, 1, '2018–01–01') as [date] 
from [range] 
where dateadd(day, 1, '2018–01–01') ≤ '2018–12–31'

This approach has many advantages

• Works on all database engines (off course you need to replace the dateadd with corresponding function)
• Simple query and easy to understand/adapt to your need
• No complex generation on the fly

The only drawback is that

• You need to create a table

Option 2: Creating a long list of number on the Fly

Another approach is to create a long list on the fly using cross join operator, the key here is to create a sequence of 10 elements and apply a cartesian product with the elements of the same table.

-- generate a list of 10*10 rows
select v from (
    select A.v * 10 + B.v from (
        select 0 as v union all  
        select 1 union all
        select 2 union all 
        select 3 union all 
        select 4 union all 
        select 5 union all 
        select 6 union all 
        select 7 union all 
        select 8 union all 
        select 9
    ) as A cross join (
        select 0 as v union all  
        select 1 union all
        select 2 union all 
        select 3 union all 
        select 4 union all 
        select 5 union all 
        select 6 union all 
        select 7 union all 
        select 8 union all 
        select 9
    ) as B
) as range order by v

To determine the maximum number of days you will get, use the following formula

days = 10 ^ number_of_tables

for example in the query above we have 2 tables so the maximum number of days one could get is 10² = 100

In the other hand, if you have the number of days needed and you want to know how many joins you should perform

number_of_tables = ceil(log(days))

according to the above formula, to get a list of 1500 days

number_of_tables = ceil(log(1500)) = 4

The advantages of this approach

• Work on all database engines
• No setup is needed before using it (no tables or procedures)
• Fast and very acceptable for reasonable number of days

The disadvantages

• Generation on the fly
• A bit complex

Option 3: Using a recursive CTE

I wouldn’t recommend this approach, and am listing it here just for reference

In simple terms this will generate a 100 days starting from 2018–01–01

WITH [range] AS (
    SELECT 0 as v
    UNION ALL
    SELECT v + 1
    FROM range t
    WHERE t.v < 100
)
SELECT dateadd(day, v, '2018-01-01') FROM [range]

The limitation of this approach

• Not efficient
• Doesn’t work with all database engines (because of CTE)
• In Sql Server the default limit of recursion is 100

In my case I always use the first approach, it’s simple and obviously the most efficient however it needs some setup (3 seconds to create the table + 1 minute to write a seeding function to seed it :D)