What is the best way to split start and end time into specified intervals in TSQL?-CodePudding

For ex:

@StartTime = '7/27/2022 7:55 AM'
@EndTime = '7/27/2022 5:07 PM'
@Interval = 30 minutes

After rounding (Round to hour and half hour):
@StartTime = '7/27/2022 8:00 AM'
@EndTime = '7/27/2022 5:00 PM'

Output:
8:00 AM - 8:30 AM
8:30 AM - 9:00 AM
.....
.....
4:30 PM - 5:00 PM

How can I do this in TSQL in SQL Server? Thanks!

CodePudding user response：

Something like this?:

DECLARE @StartTime DATETIME2(3) = '7/27/2022 7:55 AM';
DECLARE @EndTime DATETIME2(3) = '7/27/2022 5:07 PM';
DECLARE @IntervalMinutes INT = 30;

SET @StartTime = DATEADD(Minute,-DATEPART(MINUTE,@StartTime) % @IntervalMinutes,@StartTime);
SET @EndTime = DATEADD(Minute,-DATEPART(MINUTE,@EndTime) % @IntervalMinutes,@EndTime);

WITH time_intervals AS (
SELECT
    CONVERT(TIME,'00:00') as time_stamp
UNION ALL
SELECT
    DATEADD(MINUTE,@IntervalMinutes,next_cte.time_stamp)
FROM time_intervals next_cte
WHERE DATEADD(MINUTE,@IntervalMinutes,next_cte.time_stamp) <> '00:00'
)
SELECT
    time_intervals.time_stamp [From time],
    DATEADD(MINUTE,@IntervalMinutes,time_intervals.time_stamp) [To time]
FROM time_intervals
WHERE time_stamp BETWEEN CONVERT(TIME,@StartTime) AND CONVERT(TIME,@EndTime)
OPTION (MAXRECURSION 0)

This is using a recursive cte to generate a table with set intervals. Just use localisation when converting to text-datatypes to get AM/PM or handle it in presentation layer.