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