SQL Server 2022

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

CREATE TABLE TEST (MEMBER_ID int, START_DATE date, END_DATE date);
INSERT INTO TEST VALUES (1, '2023-01-01','2023-01-31'),
   (1, '2023-01-01','2023-02-28'),
   (1, '2023-01-01','2023-03-31');

CREATE TABLE CAL (cal_date date);

3 rows affected

/*populate calendar table*/
with dates ([Date]) as (
    Select convert(date,'2020-01-01') as [Date] 
    union all 
    Select dateadd(day, 1, [Date])
    from dates
    where [Date] <= '2025-12-31' 
)
INSERT INTO CAL
select [Date]
from dates
option (maxrecursion 32767)
2193 rows affected

SELECT DISTINCT MEMBER_ID,
  cal.first_of_month as START_DATE
FROM TEST
   INNER JOIN (SELECT cal_date as first_of_month FROM CAL WHERE DAY(cal_date) = 1) as cal
      ON cal.first_of_month BETWEEN TEST.START_DATE AND TEST.END_DATE


MEMBER_ID
START_DATE


1
2023-01-01

1
2023-02-01

1
2023-03-01