What I usually do when I have to figure out this type of thing on the
fly (in whatever programming language) is to jump to the first of the
next month, and then back one day. That gets you the last day of
whatever month you are talking about. Here's an example that figures
the number of days in the current month.
SELECT date_part('day',
(date_part('year', CURRENT_DATE) || '-' ||
date_part('month', CURRENT_DATE) || '-01')::date
+ '1 month'::interval
- '1 day'::interval) AS days;
This looks a little clumsy because I have to create a date for the
first day of the current month by getting the current year and and
month and using the || operator to paste these together in the form:
YYYY-MM-01, and then casting that bit of text as a date.
If this is something you do a lot it would be fairly simple to create
a function that did such a thing. In fact, just for fun I created one
myself. Simply pass in the year and month as integers and you get
your response.
CREATE FUNCTION num_days(int, int) RETURNS float8 AS -- years, months
'SELECT date_part(''day'',
(($1::text || ''-'' || $2::text || ''-01'')::date
+ ''1 month''::interval
- ''1 day''::interval)) AS days'
LANGUAGE 'sql';
And a quick test.
SELECT num_days(2000, 2), num_days(2001, 2), num_days(2002, 3);
num_days | num_days | num_days
----------+----------+----------
29 | 28 | 31
(1 row)
Now, I am not entirely sure that this is the *best* way to go about
this, but it works. Hopefully someone else will point out a better
method if one exists.
Jason
Michael Klatt <mdklatt@ou.edu> writes:
> Is there a PostgreSQL function to return the number of days in a month?
>
> ___________________________________
> Michael Klatt
>
> University of Oklahoma
> Environmental Verification and Analysis Center
> 710 Asp Avenue, Suite 8
> Norman, OK 73069
>
> 405.447.8412
> 405.447.8455 FAX
> http://www.evac.ou.edu/
>
> "I'm a great motivator. Everyone says they have to work twice as hard
> when I'm around."
>
>