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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
