Display dates between two input dates using SQL DATEDIFF function in SQL Server

This blog includes a simple script to display dates between two input dates from table columns. It uses the SQL DATEDIFF() function to return the other dates between two input dates.

I recently got a requirement where the user wants to display all dates between two dates in separate rows. How do we do that in the SQL Server? Let’s check it out.

I created a table and inserted a sample record in it to demonstrate the problem.

CREATE TABLE ABC
(
EMPID int,
Date_From date
,Date_TO date)
INSERT INTO abc VALUES (1,'2022-04-01','2022-04-15')
SELECT * FROM abc

The table has two fields – Date_From and Date_TO. The user requires displaying all dates starting from Date_From to end with Date_To column values.

Let’s run the following query for the required data.

DECLARE @MinDate DATE,
        @MaxDate DATE ,
             @Noofdays int
Set @MinDate = (Select Date_From FROM abc)
Set @MaxDate = (Select Date_TO  FROM abc);
CREATE TABLE #ReqData
(EmpID int,
dates date
)
SELECT @Noofdays= DATEDIFF(dd,@MinDate,@MaxDate)
while(@Noofdays>=0)
BEGIN
Insert into #ReqData SELECT EMPID, 
DATEADD(day, -@Noofdays, CAST(@MaxDate AS date)) 
FROM abc
SET @Noofdays=@Noofdays-1
END
SELECT * FROM #ReqData
DROP TABLE #ReqData