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.
x
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
Advertisement
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)