Transforming date with Redshift

Question

I am creating an end of month variable, that works with postgres but I have to rewrite it in Redshift, any clue why it shows me the following error?

The database reported a syntax error: Amazon Invalid operation: Interval values with month or year parts are not supported Details: ----------------------------------------------- error: Interval values with month or year parts are not supported code: 8001 context: i

select
(date_trunc('month', ("Date (id created)" + INTERVAL '1 month'))) as "EoM"
from id

Answer 1

Redshift uses dateadd to apply intervals to timestamps

select date_trunc(month, ( dateadd(month,1,"Date (id created)"))) as "EoM"
from id

see https://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html

Answer 2

Redshift supports the "last_day()" function that returns the date of the last day of any month. https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html

This is a useful function for these calculations.

Redshift cannot add a month or a year since there is no single length for these intervals. If you do want to keep on your current route you can truncate the date you are interested in to the month level, add 35 days, then truncate to month again.