Re: Date for a week day of a month

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