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
