Amazon Redshift dateadd() in CASE statement

Question

I'm trying to add the date in a case statement

select the_date, 
(case 
when the_date like '%years%' then dateadd(year,REPLACE(REGEXP_SUBSTR(condition_, '-?[0-9]+\.? years'),'years',''),the_date)
end) 
from customer

But I'm getting the following error:

SQL Error [500310] [42883]: Amazon Invalid operation: function pg_catalog.date_add("unknown", text, timestamp with time zone) does not exist;

Can anyone help please?

botchniaque botchniaque · Accepted Answer · 2019-08-13T08:25:30

You have to cast the second parameter to INT and the third parameter to timestamp (currently it's timestamptz.

This is the DATEADD signature:

DATEADD( datepart, interval, {date|timestamp} )

Your query should look something like:

select the_date, 
(case when the_date like '%years%' then 
    dateadd(
         year,
         REPLACE(REGEXP_SUBSTR(condition_, '-?[0-9]+\.? years'),'years','')::INT,
         the_date::timestamp
    )
end) 
from customer

Alternatively you could use simple + operator with an interval. This way you can preserve the timestamptz data type for the_date:

select the_date, 
(case when the_date like '%years%' then 
    the_date + (interval '1 year' * REPLACE(REGEXP_SUBSTR(condition_, '-?[0-9]+\.? years'),'years','')::INT)
    )
end) 
from customer

Amazon Redshift dateadd() in CASE statement

1 Answers