On Jul 3, 2007, at 14:54 , Emi Lu wrote:
result := (
(date_part('year', $1) || '-' || date_part('month',
$1) || '-01')::date
+ '1 month'::interval - '1 day'::interval
)::date;
I recommend not using string manipulation to handle data that is not
textual. There are a lot of date and time functions available. The
above can be rewritten in a couple of different ways:
result := (date_trunc('month', $1) + interval '1 month' - interval '1
day')::date;
result := (date_trunc('month', $1 + interval '1 month'))::date - 1;
For example:
SELECT current_date
, (date_trunc('month', current_date) + interval '1 month' -
interval '1 day')::date as all_intervals
, (date_trunc('month', current_date + interval '1 month'))::date
- 1 as date_arithmetic;
date | all_intervals | date_arithmetic
------------+---------------+-----------------
2007-07-03 | 2007-07-31 | 2007-07-31
(1 row)
Hope this helps.
Michael Glaesemann
grzm seespotcode net
