s

postgresql function to get number of days in a month edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 28 January 2022 | 1755

You can call no direct function to get the number of days in a month from a date in PostgreSQL. In MSSQL, it's straightforward; you can use the day and eomonth function to get the number of days, but we don't have the eomonth function in PostgreSQL. So here is a function I have written to get the number of data in a month given a date.

PostgreSQL function monthnumberofdays

CREATE OR REPLACE FUNCTION public.fn_numberofdays_inmonth(
	_date date)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
	retval numeric;
begin
	retval = DATE_PART('day',DATE_TRUNC('month', _date)   interval '1 month' - interval '1 day')::int; 
	return retval;
end;
$BODY$;