generate_series that's a good one!
Thank you!
> On 7/3/07, Emi Lu <emilu@encs.concordia.ca> wrote:
>>
>> Can I know how to get the date of each month's last Thursday please?
>> Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
>> Result: 2007-04-26
>>
>
> you can easily do it without functions.
> for example, this select:
> SELECT
> cast(d.date + i * '1 day'::interval as date)
> FROM
> (select '2007-04-01'::date as date) d,
> generate_series(0, 30) i
> WHERE
> to_char(d.date, 'MM') = to_char( cast(d.date + i * '1 day'::interval as
> date), 'MM')
> AND to_char(cast(d.date + i * '1 day'::interval as date), 'D') = '5'
> ORDER BY 1 DESC
> LIMIT 1
> ;
> does what you need.
> to get last-thursday for another month, just change: (select
> '2007-04-01'::date as date) d, to be 1st of any other month.
>
> depesz
>