Redshift Interval DateAdd

Redshift Interval DateAdd.sql

-- Solution to use 'dateadd' function to add years, instead of 'interval'
WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(year,'2000-01-01'::date,trunc(current_date)) 
),
date_list as (
    SELECT n as id,dateadd(year,n,trunc('2000-01-01'::date)) as date_v FROM numbers
  )
 select *,extract(week from date_v) from date_list

--****************************************************************
-- Fails with dynamic variable to add year to date
WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(year,'1980-01-01'::date,trunc(current_date)) 
),
date_list as (
    SELECT n as id,trunc('1980-01-01'::date + n * interval '1 year') as date_v FROM numbers
  )
 select * from date_list
 --****************************************************************
-- Succeeds with static value to add year to date
WITH recursive numbers(n) as
( 
  SELECT 0 as n
    UNION all
    SELECT n + 1
    FROM numbers n
    WHERE n.n <= datediff(year,'1980-01-01'::date,trunc(current_date)) 
),
date_list as (
    SELECT n as id,trunc('1980-01-01'::date + 10 * interval '1 year') as date_v FROM numbers
  )
 select * from date_list
 --****************************************************************