Viswamitra's blog

Below is the functions to get first day and last day of month in PostgreSQL.

To get first day of month, below is the plpgsql function, this function take the date value as input and return the first day of that month.

CREATE OR REPLACE FUNCTION first_day(date)
RETURNS date AS
$$
SELECT (date_trunc('MONTH', $1))::date;
$$ LANGUAGE 'sql'
IMMUTABLE STRICT;

part=# select first_day('2020-07-03');
 first_day
------------
 2020-07-01
(1 row)

To get the last of month, below is the plpgsql function, this function take the date value as input and return the last day of month.

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;

part=# select last_day('2020-07-03');
  last_day
------------
 2020-07-31
(1 row)