Examples of generating spines/dates in SQL. Assisted by Caleb Kassa.

sql-spines.md

Spines in SQL

Given a starting date 2024-02-01 I would like to generate 7 days into the future until February 8th (2024-02-08), ex.g.

dt
2024-02-01
2024-02-02
2024-02-03
2024-02-04
2024-02-05
2024-02-06
2024-02-07
2024-02-08

Snowflake

Using Table Generator

/* Date Spine Example: Specific number of days */
with date_spine as (
    SELECT dateadd(day, '+' || seq4(), '2024-02-01')::DATE as dt
    FROM
      TABLE
        (generator(rowcount => 8)) -- edit to change number of days
)
SELECT dt 
from date_spine;

Using Recursive CTE

/* Date Spine Example: Start and End Dates */
set start_date = '2024-02-01'::DATE;
set end_date = current_date();
WITH RECURSIVE date_spine AS (
    SELECT $start_date as dt
    UNION ALL
    SELECT dateadd(day,1,dt) as dt -- date granularity
    FROM date_spine
    WHERE dt < $end_date -- end date (inclusive)
)
SELECT dt
FROM date_spine;