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!
>>>

It turns out my original solution was slightly (badly) wrong and was
returning seemingly random numbers ;-) Here is a modified version that
seems to do the trick.

CREATE OR REPLACE FUNCTION lastday (date,int) RETURNS DATE AS '
DECLARE
    last_day date;
    last_dow int;
BEGIN
    last_day := date_trunc(''month'', $1) + ''1 month''::interval - ''1
day''::interval;
    last_dow := $2 - EXTRACT(dow FROM last_day)::int;
    if (last_dow > 0) then
        last_dow := last_dow - 7;
    end if;
    RETURN last_day + (''1 day''::interval * last_dow);
END;
' LANGUAGE plpgsql;

Use the same as the previous version, the second parameter is 0-6, where
0 is sunday. The first input is the date, and this time it doesnt have
to be the first day of the month.

xxxx=# select '2007-04-01', lastday('2007-04-01', 4);
  ?column?  |  lastday
------------+------------
 2007-04-01 | 2007-04-26

jnb198_chuckie=# select '2007-04-10', lastday('2007-04-10', 4);
  ?column?  |  lastday
------------+------------
 2007-04-10 | 2007-04-26


Nick