How to get last sunday of march and october month using postgre sql?
I am looking to fetch last Sunday of march and October every year.
select current_date – extract(dow from current_date)::integer;
This gives me the last sunday
>Solution :
Here is a list of last March and October sundays from year 2000 till 2100. Take the first day of the next month (April, November) for the year, truncate it to the beginning of the week and subtract one day. Do this for every year.
with t(y) as (select generate_series(0, 100, 1))
select
date_trunc('week', '2000-04-01'::date + interval '1 year' * y)::date - 1 ls_march,
date_trunc('week', '2000-11-01'::date + interval '1 year' * y)::date - 1 ls_october
from t;