Re: Date for a week day of a month

Emi Lu wrote:
> 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