dates from the DATEDIFF()

Question

I am using the query in Snowflake:

select DATEDIFF(day,start_date ,end_date) as days
       ,start_date
       ,end_date 
from table1

It gives me no. of days as:

days	start_date	end_date
14	2022-09-03	2022-09-17
28	2022-08-19	2022-09-16

but I need to find the dates for the days instead of just the no. of days i.e I want to see those all 14 dates instead of just no. of days.

can anyone help.

Answer 1

so we will create a table like your example data:

create table date_data(start_date date, end_date date) as
  select * from values
  ('2022-09-03'::date, '2022-09-17'::date),
  ('2022-08-19'::date, '2022-09-16'::date);

and use your example SQL:

select DATEDIFF(day,start_date ,end_date) as days
       ,start_date
       ,end_date 
from date_data;

we get:

DAYS	START_DATE	END_DATE
14	2022-09-03	2022-09-17
28	2022-08-19	2022-09-16

but "we want all the dates between these"

so lets use a table generator to make some rows (GENERATOR), and for each row we will allocate a incrementing number with no gaps (ROW_NUMBER), and then add those numbers so a known date to build a range of dates (DATE_FROM_PART, DATEADD):

select dateadd('day', row_number() over (order by null)-1, date_from_parts(1900,1, 1)) as date
from table(generator(ROWCOUNT => 10));

DATE
1900-01-01
1900-01-02
1900-01-03
1900-01-04
1900-01-05
1900-01-06
1900-01-07
1900-01-08
1900-01-09
1900-01-10

Now 10 is not enouth, and 1900 is too early, so we can change those value.

Now we can build a date table, which is a wise thing to do, if you are going to work with date ranges a lot:

create table big_table_of_date as
select dateadd('day', row_number() over (order by null)-1, date_from_parts(1900,1, 1)) as date
from table(generator(ROWCOUNT => 1000));

or if you are doing a one off calculation you can use a CTE to hold those values, but this will be generated every time this SQL is run, so might be "costly" if run over large ranges millions of times...

with cte_big_table_of_date as (
    select dateadd('day', row_number() over (order by null)-1, date_from_parts(2022,1, 1)) as date
    from table(generator(ROWCOUNT => 1000))
)  
select        
    d.start_date
   ,d.end_date 
   ,r.date
from date_data as d
join cte_big_table_of_date as r
    on r.date between d.start_date and d.end_date
order by 1,2,3;

START_DATE	END_DATE	DATE
2022-08-19	2022-09-16	2022-08-19
2022-08-19	2022-09-16	2022-08-20
2022-08-19	2022-09-16	2022-08-21
2022-08-19	2022-09-16	2022-08-22
2022-08-19	2022-09-16	2022-08-23
2022-08-19	2022-09-16	2022-08-24
2022-08-19	2022-09-16	2022-08-25
2022-08-19	2022-09-16	2022-08-26
2022-08-19	2022-09-16	2022-08-27
2022-08-19	2022-09-16	2022-08-28
2022-08-19	2022-09-16	2022-08-29
2022-08-19	2022-09-16	2022-08-30
2022-08-19	2022-09-16	2022-08-31
2022-08-19	2022-09-16	2022-09-01
2022-08-19	2022-09-16	2022-09-02
2022-08-19	2022-09-16	2022-09-03
2022-08-19	2022-09-16	2022-09-04
2022-08-19	2022-09-16	2022-09-05
2022-08-19	2022-09-16	2022-09-06
2022-08-19	2022-09-16	2022-09-07
2022-08-19	2022-09-16	2022-09-08
2022-08-19	2022-09-16	2022-09-09
2022-08-19	2022-09-16	2022-09-10
2022-08-19	2022-09-16	2022-09-11
2022-08-19	2022-09-16	2022-09-12
2022-08-19	2022-09-16	2022-09-13
2022-08-19	2022-09-16	2022-09-14
2022-08-19	2022-09-16	2022-09-15
2022-08-19	2022-09-16	2022-09-16
2022-09-03	2022-09-17	2022-09-03
2022-09-03	2022-09-17	2022-09-04
2022-09-03	2022-09-17	2022-09-05
2022-09-03	2022-09-17	2022-09-06
2022-09-03	2022-09-17	2022-09-07
2022-09-03	2022-09-17	2022-09-08
2022-09-03	2022-09-17	2022-09-09
2022-09-03	2022-09-17	2022-09-10
2022-09-03	2022-09-17	2022-09-11
2022-09-03	2022-09-17	2022-09-12
2022-09-03	2022-09-17	2022-09-13
2022-09-03	2022-09-17	2022-09-14
2022-09-03	2022-09-17	2022-09-15
2022-09-03	2022-09-17	2022-09-16
2022-09-03	2022-09-17	2022-09-17

Answer 2

The dates could be generated by multiplying rows per difference of days:

SELECT table1.start_date
      ,table1.end_date
      ,table1.start_date + ROW_NUMBER() OVER(PARTITION BY table1.start_date,
                                                          table1.end_date 
                                             ORDER BY NULL)-1 AS generated_date
FROM table1
, TABLE(SPLIT_TO_TABLE(SPACE(DATEDIFF('day', table1.start_date, table1.end_date))
        , ' ')) AS r;

For sample data:

CREATE OR REPLACE TABLE table1
AS
SELECT '2022-09-03'::DATE AS start_date, '2022-09-17'::DATE AS end_date UNION ALL
SELECT '2022-08-19'::DATE, '2022-09-16'::DATE;

Output (part):

Answer 3

You can generate a derived table that holds all dates in the overall date range needed for this table and then use that to your join your table back in:

WITH CTE_MY_DATE AS (
    SELECT DATEADD(DAY, SEQ4(), '2000-01-01') AS MY_DATE
    FROM TABLE(GENERATOR(ROWCOUNT=>10000))  
    WHERE MY_DATE BETWEEN 
        (SELECT MIN(start_date) FROM table1)
        AND 
        (SELECT MAX(end_date) FROM table1)
)
SELECT nct.calendar_date_column
   start_date, end_date
FROM new_calendar_table nct
   INNER JOIN table1 
      ON nct.calendar_date_column BETWEEN table1.start_date and table1.end_date