Returning Valid Time Slots with a Given Interval

Question

I am trying to return all the valid time slots between a start and end time with a given interval via SQL, but am having a mental block big time. Below is some sample code with some variables and then a table to store each value. The variables are using INT, as that is the data type where the values will eventually be stored is INT, and can't be changed

DECLARE @StartTime INT = 900
DECLARE @EndTime INT = 2000
DECLARE @CurrentTime INT = 900
DECLARE @Interval INT = 15

DECLARE @Times TABLE
(
    [Time] INT
)

WHILE (@CurrentTime <= @EndTime)
BEGIN
    INSERT INTO @Times VALUES (@CurrentTime)

    SET @CurrentTime = @CurrentTime + @Interval
END

When the above runs it populates, but gives me invalid values. Values returned:

The desired results are:

Is there some way to do this, by dividing the current value and expecting a remainder, or an easy way to achieve the desired results?

Thanks

time portion mod 60 ............. – Mitch Wheat Aug 14 '13 at 08:26 — Mitch Wheat, Aug 14 '13 at 08:26

score 2 · Answer 1 · answered Aug 14 '13 at 08:35

Try this

DECLARE @StartTime INT = 900
DECLARE @EndTime INT = 2000
DECLARE @CurrentTime INT = 900
DECLARE @Interval INT = 15

DECLARE @Times TABLE
(
    [Time] INT
)

WHILE (@CurrentTime <= @EndTime)
BEGIN
    INSERT INTO @Times VALUES (@CurrentTime)

    SET @CurrentTime = @CurrentTime + @Interval

    IF(RIGHT(@CurrentTime,2) = 60)
        SET @CurrentTime = @CurrentTime + 40

END

SELECT * FROM @Times

score 1 · Accepted Answer · edited May 23 '17 at 12:05

This is a bit more complex, but has several differences over the above answer: it can bridge midnight, handle intervals that aren't factors of 60 (try the above with an interval of 7), and uses a recursive CTE to generate the numbers instead of a WHILE loop.

Its a shame about the INT datatype, with all the bonuses of TSQL's TIME datatype. You could do away with the helper functions.

The MAXRECURSION option is sometimes problematic, as you can't use it inside of a view... the calling statement has to specify it. To get around this, wrap it in a multi-statement table-valued function. see this post

CREATE FUNCTION dbo.ConvertIntToTime (@TimeAsInt INT)
RETURNS TIME
BEGIN

    DECLARE @TimeString VARCHAR(4) = RIGHT(REPLICATE('0',4) + CAST(@TimeAsInt AS VARCHAR(4)),4);
    RETURN PARSE(LEFT(@TimeString,2) + ':' + RIGHT(@TimeString,2) AS TIME);

END;
GO

CREATE FUNCTION dbo.ConvertTimeToInt (@Time TIME)
RETURNS INT
BEGIN

    DECLARE @TimeString VARCHAR(5) = CONVERT(VARCHAR(5), @Time, 108);
    RETURN PARSE(LEFT(@TimeString,2) + RIGHT(@TimeString,2) AS INT); 

END;
GO



DECLARE @CurrentTime AS INT = 900;
DECLARE @EndTime AS INT = 1700;
DECLARE @Interval AS INT = 10;

DECLARE @CurrentTimeAsTime TIME = dbo.ConvertIntToTime(@CurrentTime);
DECLARE @EndTimeAsTime TIME = dbo.ConvertIntToTime(@EndTime);

WITH CTEGenerateTimes AS
(

    SELECT  @CurrentTimeAsTime Time

    UNION ALL

    SELECT DATEADD(MINUTE, @Interval, Time) AS TIME
    FROM CTEGenerateTimes
    WHERE   (@EndTimeAsTime >= @CurrentTimeAsTime AND DATEADD(MINUTE, @Interval, Time) <= @EndTimeAsTime AND DATEADD(MINUTE, @Interval, Time) <> CAST('00:00' AS TIME)) --Range doesn't cross midnight
            OR (@EndTimeAsTime < @CurrentTimeAsTime AND DATEADD(MINUTE, @Interval, Time) > @CurrentTimeAsTime AND DATEADD(MINUTE, @Interval, Time) <= CAST('23:59:59' AS TIME))  --Range crosses midnight, portion before midnight
            OR (@EndTimeAsTime < @CurrentTimeAsTime AND DATEADD(MINUTE, @Interval, Time) <= @CurrentTimeAsTime AND DATEADD(MINUTE, @Interval, Time) <= @EndTimeAsTime)

)

SELECT dbo.ConvertTimeToInt(t.Time) Time
FROM CTEGenerateTimes t
OPTION (MAXRECURSION 1441)

Returning Valid Time Slots with a Given Interval

2 Answers2