To obtain the number of days in a month, I wrote this function:
CREATE FUNCTION dayCountOfMonth(datetime) RETURNS float AS
' DECLARE
theDate ALIAS FOR $1;
monthStart date;
monthEnd date;
BEGIN
monthStart := DATE_TRUNC(''month'', theDate);
monthEnd := monthStart + ''1 month''::timespan - ''1 day''::timespan;
RETURN DATE_PART(''doy'', monthEnd) - DATE_PART(''doy'', monthStart) + 1;
END;
' LANGUAGE 'PL/pgSQL';
It seems to work, except with the month of October (10).
dayCountOfMonth('1997-10-1') => 30
dayCountOfMonth('1998-10-1') => 30
dayCountOfMonth('1999-10-1') => 31
dayCountOfMonth('2000-10-1') => 30
dayCountOfMonth('2001-10-1') => 30
dayCountOfMonth('2002-10-1') => 30
dayCountOfMonth('2003-10-1') => 30
dayCountOfMonth('2004-10-1') => 31
Just one question: WHY??????
(Note: no trouble with February)
Is there a function that give the number of days of a month?
Thanks,
Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64