dates from the DATEDIFF()

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

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