Generate list of all dates between x & y

I am tyring to use a solution I found online to generate a table of all dates between yesterday and Jan 1st of the prior year.

SELECT CAST(date_column AS DATE) AS DAY
FROM (
      VALUES (SEQUENCE(cast((YEAR(CURRENT_DATE)-1)||'-01-01' AS date), CURRENT_DATE - INTERVAL '1' DAY , INTERVAL '1' DAY) ) 
    ) AS t1(date_array)
CROSS JOIN UNNEST(date_array) AS t2(date_column)

I’m getting errors on the 3rd line since i’m not sure how to dynamically build the right date for prior year that I want. If the query were to run today I would expect a list of all dates from Jan 1st 2019 to Apr 16th 2020

Answer

Hmmm . . . Does this work?

SELECT t2.date_column
FROM (VALUES (SEQUENCE(DATE_TRUNC('year', CURRENT_DATE), CURRENT_DATE - INTERVAL '1' DAY ) ) 
     ) t(date_array) CROSS JOIN
     UNNEST(v.date_array) t2(day)

Advertisement

Answer