'Transforming date with Redshift

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
sqldateamazon-redshiftintervals


Solution 1:[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

Solution 2:[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.

Solution 3:[3]

If you are looking to get days of a month, where your timestamp is first day of the month

SELECT datediff(day,timestamp,dateadd(month,1,timestamp))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Bill Weiner
Solution 3 Polsani Sandeep

Related Questions