SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table REF_Calendar (
 cal_date date primary key not null,
 cal_year smallint not null,
 cal_month tinyint not null,
 cal_day tinyint not null,
 cal_dayofyear smallint not null,
 cal_quarter tinyint not null,
 cal_weekday_abbrev char(3) not null,
 cal_iso_week tinyint,
 cal_df7_week tinyint not null,
 cal_df7_weekday tinyint not null,
 cal_df1_week tinyint,
 cal_df1_weekday tinyint,
 cal_mooncycle tinyint,
 index idx_Calendar_year_week unique (cal_year, cal_df7_week, cal_df7_weekday)
);
SET DATEFIRST 7; -- 7: week starts on sunday

declare @startdate date = '2020-01-01';
declare @enddate   date = '2023-01-01';

with rcte_calendar as (
  select @startdate as cal_date
  union all
  select dateadd(day, 1, cal_date)
  from rcte_calendar
  where cal_date < @enddate
)
insert into ref_calendar 
(cal_date, cal_year, cal_month, cal_day, cal_dayofyear, cal_quarter, cal_weekday_abbrev, 
 cal_df7_week, cal_df7_weekday, cal_mooncycle)
select
  cal_date
, datepart(year, cal_date) as cal_year
, datepart(month, cal_date) as cal_month
, datepart(day, cal_date) as cal_day
, datepart(dayofyear, cal_date) as cal_dayofyear
, datepart(quarter, cal_date) as cal_quarter
, lower(left(datename(weekday, cal_date), 3)) as cal_weekday_abbrev
, datepart(week, cal_date) as cal_df7_week
, datepart(weekday, cal_date) as cal_df7_weekday
, abs(1-abs(round(((14-1.0*CONVERT(int, CONVERT(nvarchar(2), cal_date, 131)))/14)
 , 1, 1)))*100 as cal_mooncycle
from rcte_calendar cte
where not exists (
        select 1 
        from ref_calendar cal
        where cal.cal_date = cte.cal_date
      )
option (maxrecursion 0);
1097 rows affected

SET DATEFIRST 1; -- 1: week starts on monday
update ref_calendar
set cal_df1_week = datepart(week, cal_date)
  , cal_df1_weekday = datepart(weekday, cal_date)
  , cal_iso_week = datepart(iso_week, cal_date)
where cal_df1_week is null
   or cal_df1_weekday is null
   or cal_iso_week is null;
1097 rows affected

select top 9 * 
from ref_calendar 
where cal_mooncycle = 100
  and cal_date >= getdate()
  and cal_year <= 1 + datepart(year, current_timestamp)
order by cal_date asc;


cal_date
cal_year
cal_month
cal_day
cal_dayofyear
cal_quarter
cal_weekday_abbrev
cal_iso_week
cal_df7_week
cal_df7_weekday
cal_df1_week
cal_df1_weekday
cal_mooncycle


DECLARE @Test TABLE (col char(7)); 
INSERT INTO @Test VALUES 
  ('2021-01'), ('2021-02')
, ('2021-48')
, ('2022-01'), ('2022-02')
;

select *
from @test t
outer apply (
  select 
    min(cal_date) as startofweek
  , max(cal_date) as endofweek
  from (values (try_cast(left(col, 4) as int),
            abs(try_cast(right(col, 2) as int))) 
  ) as val(yr, ww)
  join ref_calendar as cal
    on ((cal_year = yr and cal_df7_week = ww)
        or (ww = 1  and cal_year = yr-1 and cal_df7_week = 53) 
        or (ww = 53 and cal_year = yr+1 and cal_df7_week = 1))
) cal


col
startofweek
endofweek


2021-01
2020-12-27
2021-01-02

2021-02
2021-01-03
2021-01-09

2021-48
2021-11-21
2021-11-27

2022-01
2021-12-26
2022-01-01

2022-02
2022-01-02
2022-01-08
col	startofweek	endofweek
2021-01	2020-12-27	2021-01-02
2021-02	2021-01-03	2021-01-09
2021-48	2021-11-21	2021-11-27
2022-01	2021-12-26	2022-01-01
2022-02	2022-01-02	2022-01-08