Friday, October 31, 2014

Postgres get First or Last day of the month


-- Last day of the month
CREATE OR REPLACE FUNCTION public.fn_getlastofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (to_char(($1 + interval '1 month'),'YYYY-MM') || '-01')::date - 1;
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


-- First day of month
CREATE OR REPLACE FUNCTION public.fn_getfirstofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (date_trunc('MONTH', $1)::DATE);
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Usage:
SELECT fn_getlastofmonth('2014-08-03');
SELECT fn_getfirstofmonth('2014-08-03');

No comments:

Post a Comment