Blog Pages

SQL - select dates list between dates

DECLARE
@StartDate DATETIME, -- date and time
@Duration INT,
@ToDate DATETIME;

SELECT
        @StartDate = '2021-07-28 08:00:00.000',
@Duration = 60,
@ToDate = '2021-08-03 08:00:00.000';

DECLARE @l_Date DATETIME;
SET @l_Date = CAST (@StartDate AS DATE);


; WITH GetDates AS
(  
    SELECT @StartDate AS OccupiedDate, @l_Date AS CalDate
    UNION ALL  
    SELECT DATEADD(HOUR ,24, OccupiedDate) AS OccupiedDate, DATEADD(HOUR ,24, CalDate) AS CalDate
FROM GetDates  
    WHERE OccupiedDate < @ToDate  
)  
SELECT FROM GetDates;


; WITH GetDates AS
(  
SELECT @l_Date AS CalDate
UNION ALL  
SELECT DATEADD(HOUR ,24, CalDate) AS CalDate
FROM GetDates  
WHERE DATEADD(HOUR ,24, CalDate) < @ToDate  
)
SELECT FROM GetDates;