SQL Server 2019 (Linux)

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

-- Create the Events table
CREATE TABLE Events (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(255),
    FromDate DATE,
    ToDate DATE
);

-- Populate the table with two records
INSERT INTO Events (Name, FromDate, ToDate)
VALUES ('Event 1', '2023-11-10', '2023-11-11'),
       ('Event 2', '2023-11-15', '2023-11-17');
2 rows affected

SELECT * FROM Events;


ID
Name
FromDate
ToDate


1
Event 1
2023-11-10
2023-11-11

2
Event 2
2023-11-15
2023-11-17


-- Common Table Expression (CTE) to expand date ranges
WITH EventsExpanded
AS (
	SELECT ID
		,Name
		,FromDate AS EventDate
		,FromDate
		,ToDate
	FROM Events
	
	UNION ALL
	
	SELECT ID
		,Name
		,DATEADD(DAY, 1, EventDate)
		,FromDate
		,ToDate
	FROM EventsExpanded
	WHERE DATEADD(DAY, 1, EventDate) <= ToDate
	)
SELECT ID
	,Name
	,EventDate
	,FromDate
	,ToDate
FROM EventsExpanded
ORDER BY ID
	,EventDate
-- Recursion depth 364 will support maximum event date range of 365 days
OPTION (MAXRECURSION 364);


ID
Name
EventDate
FromDate
ToDate


1
Event 1
2023-11-10
2023-11-10
2023-11-11

1
Event 1
2023-11-11
2023-11-10
2023-11-11

2
Event 2
2023-11-15
2023-11-15
2023-11-17

2
Event 2
2023-11-16
2023-11-15
2023-11-17

2
Event 2
2023-11-17
2023-11-15
2023-11-17

ID	Name	EventDate	FromDate	ToDate
1	Event 1	2023-11-10	2023-11-10	2023-11-11
1	Event 1	2023-11-11	2023-11-10	2023-11-11
2	Event 2	2023-11-15	2023-11-15	2023-11-17
2	Event 2	2023-11-16	2023-11-15	2023-11-17
2	Event 2	2023-11-17	2023-11-15	2023-11-17