Re: Date for a week day of a month

Thank you all for your inputs!

Based on your inputs, made it a bit change to my application:
==============================================================================

DROP FUNCTION              test_db.lastWeekdayDate (date, varchar) ;
CREATE OR REPLACE FUNCTION test_db.lastWeekdayDate (date, varchar)
RETURNS DATE AS $$
DECLARE
    result   date;

BEGIN
    result :=  (
                 (date_part('year', $1) || '-' || date_part('month', $1)
|| '-01')::date
                  + '1 month'::interval - '1 day'::interval
               )::date;

    WHILE to_char(result, 'DY') <> $2 LOOP
       result := result - '1 day'::interval ;
    END LOOP;

    RETURN result ;
END;
$$ language 'plpgsql';


select lastWeekdayDate('2007-07-03', 'THU');

  lastweekdaydate
-----------------
  2007-07-26
(1 row)




>> Hello,
>>
>> Can I know how to get the date of each month's last Thursday please?
>>
>> For example, something like
>>
>> Query:  select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
>> Result: 2007-04-26
>>
>> Thank you!
>>
>
>
> CREATE OR REPLACE FUNCTION lastday (date,int) RETURNS DATE AS '
> DECLARE
>    result   date;
>    last_day date;
>    last_dow int;
> BEGIN
>    last_day := $1 + ''1 month''::interval - ''1 day''::interval;
>    last_dow := EXTRACT(dow FROM last_day)::int - $2;
>    RETURN last_day + (''1 day''::interval * last_dow);
> END;
> ' LANGUAGE plpgsql;
>
>
> xxxx=# select lastday('2007-04-01', 5);
>  lastday
> ------------
> 2007-04-26
> (1 row)
>
> The second parameter is the day of the week that you want, which has the
> same spec as EXTRACT(dow FROM...). Values are from 0-6 where 0 is Sunday.
>
> Enjoy!
>
> Nick