Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create or replace 
function week_dates( do_date_in date) 
 returns table (ween_num integer, first_date date, last_date date)
 language sql 
 immutable strict
as $$
    with recursive date_list(week_num,first_date,terminate_date) as 
         ( select 1
                , date_trunc('month', do_date_in)::timestamp
                , (date_trunc('month', do_date_in) + interval '1 month' - interval '1 day')::timestamp
           union all 
           select week_num+1, (first_date+interval '7 day'), terminate_date
             from date_list
            where first_date+interval '6 day'  < terminate_date::timestamp    
         ) 
    select week_num
         , first_date::date
         , case when (first_date+interval '6 day')::date > terminate_date 
                then terminate_date::date 
                else (first_date+interval '6 day')::date 
           end last_date
    from date_list;
$$; 
 select * from week_dates(current_date);


ween_num
first_date
last_date


1
2021-03-01
2021-03-07

2
2021-03-08
2021-03-14

3
2021-03-15
2021-03-21

4
2021-03-22
2021-03-28

5
2021-03-29
2021-03-31


-- oops month changed
select * from week_dates(date '2021-02-10');


ween_num
first_date
last_date


1
2021-02-01
2021-02-07

2
2021-02-08
2021-02-14

3
2021-02-15
2021-02-21

4
2021-02-22
2021-02-28

ween_num	first_date	last_date
1	2021-03-01	2021-03-07
2	2021-03-08	2021-03-14
3	2021-03-15	2021-03-21
4	2021-03-22	2021-03-28
5	2021-03-29	2021-03-31

ween_num	first_date	last_date
1	2021-02-01	2021-02-07
2	2021-02-08	2021-02-14
3	2021-02-15	2021-02-21
4	2021-02-22	2021-02-28