How to get the last day of month in postgres?

Question

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using

to_date("act_dt",'YYYYMMDD') AS "act date" 

then find the last day of this date: like this:

(select (date_trunc('MONTH',to_date("act_dt",'YYYYMMDD')) + INTERVAL '1 MONTH - 1 day')::date) 

but it gives me this error:

ERROR: Interval values with month or year parts are not supported   Detail:    -----------------------------------------------   error:  Interval values with month or year parts are not supported   code:      8001   context:   interval months: "1"   query:     673376   location:  cg_constmanager.cpp:145   process:   padbmaster [pid=20937]   ----------------------------------------------- 

Any help?

Postgres version:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.874

Answer 1

For anybody coming to this question looking for the Postgres way to do this (not using Redshift), here's how you'd do it:

SELECT (date_trunc('month', '2017-01-05'::date) + interval '1 month' - interval '1 day')::date AS end_of_month; 

Replacing the '2017-01-05' with whatever date you want to use. You can make this into a function like this:

create function end_of_month(date) returns date as $$ select (date_trunc('month', $1) + interval '1 month' - interval '1 day')::date; $$ language 'sql' immutable strict; 

EDIT Postgres 11+

Pulling this out of the comments from @Gabriel, you can now combine interval expressions in one interval (which makes things a little shorter):

select (date_trunc('month', now()) + interval '1 month - 1 day')::date as end_of_month;  -- +--------------+ -- | end_of_month | -- +--------------+ -- | 2021-11-30   | -- +--------------+ -- (1 row)