[GENERAL] Getting number of days in a month

[Guillaume Perréal]

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