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.

What does "The date for the days" mean? What date would you want to see instead of the number `14`? I ask because when you subtract a date from another date, you get an interval, not another date; so the question is confusing. — JNevill, Oct 17 '22 at 14:08
I want to see those all 14 dates instead of just no. of days — JohnB, Oct 17 '22 at 14:10
The interval you are saying between the start and end date, I want dates for those — JohnB, Oct 17 '22 at 14:11
"All 14 dates". That makes a lot of sense! Thanks for clarifying. — JNevill, Oct 17 '22 at 14:15

Lukasz Szozda · Answer 1 · 2022-10-17T14:31:56.363

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):

edited Oct 17 '22 at 14:31

answered Oct 17 '22 at 14:24

Lukasz Szozda

162,964
23
234
275

I tried using this but it gives me some different output – JohnB Oct 18 '22 at 05:03
Hello, when I tried this way dates getting generated which are not meant to like 2050, 2109 etc – JohnB Oct 18 '22 at 07:27
@swarajnavthare The code is working for provided example. Most likely you have duplicates of dates(the same range for many rows in original table). You should PARTITION BY unique columns set which were not provided in the original question – Lukasz Szozda Oct 18 '22 at 07:28
could you please share how can I do that, I tried multiple ways but dose not seem to work, it give for example startdate : 2022-05-14 and end date : 2022-06-04 generated date upto :2024 or more – JohnB Oct 18 '22 at 07:41

score 1 · Accepted Answer · answered Oct 18 '22 at 08:18

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

Thank you so much @SimeonPilgrim this worked perfectlly, all we have to want is to not include the last date, i.e end date ex. 2022-09-16, how can we do this — JohnB, Oct 18 '22 at 11:04
we also have dates for 2023 in that how should we use this as, its only applying for 2022 — JohnB, Oct 18 '22 at 12:23
if you want the end date to not be in the results add to the join clause `on r.date between d.start_date and d.end_date and r.date < d.end_date` and if have a large date range, put a larger number in the generator. — Simeon Pilgrim, Oct 18 '22 at 19:53

score 0 · Answer 3 · answered Oct 17 '22 at 14:27

0

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

answered Oct 17 '22 at 14:27

JNevill

46,980
4
38
63

do I need to create a new table new_calendar_table for this – JohnB Oct 18 '22 at 05:06
never use `SEQx()` for the numbers as they can have gaps (as per the doc's) only use `ROW_NUMBER()...` – Simeon Pilgrim Oct 18 '22 at 08:03

dates from the DATEDIFF()

3 Answers3