LAST_DAY function in postgresql

Well, In postgres, it seems there’s no such function equivalent to LAST_DAY() available in oracle.

If you need to, you can have your own in the following ways as a

Select Query

SELECT (date_trunc('MONTH', now()) + INTERVAL '1 MONTH - 1 day')::date;

plsql Function

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS
$$
SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$ LANGUAGE 'sql'
IMMUTABLE STRICT;

Hope this helps.

Share
Improve this answer
Follow

create or replace funCtion last_day(fromdt anyelement)
returns date as
$BODY$
  SELECT (date_trunc('MONTH', cast(fromdt as date)) + INTERVAL '1 MONTH - 1 day')::date;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION last_day(anyelement) OWNER TO postgres;