Listing All Time Between Two Dates in SQL Server

Hello everyone,

In this article, I will try to give information about listing all the times of the day between two dates in SQL Server.

In SQL Server, in some cases you may want to list all the times between two dates.

You can easily do this using the code below.

DECLARE @minDateTime AS DATETIME;
DECLARE @maxDateTime AS DATETIME;

SET @minDateTime = '2023-01-01 02:00:00';
SET @maxDateTime = '2023-02-28 02:00:00';

;
WITH Dates_CTE
AS (SELECT @minDateTime AS Dates
    UNION ALL
    SELECT DATEADD(hh, 1, Dates)
    FROM Dates_CTE
    WHERE Dates < @maxDateTime)
SELECT *
FROM Dates_CTE
OPTION (MAXRECURSION 0);

When you create and run the above code, you will see a result similar to the one below.

As you can see, we have listed all the hours between the two dates.

Good luck to everyone in business and life.