sql sever判断时间范围

Question

sql sever中有两时间，开始时间：'2023-08-17 12:28:07.0000000'，结束时间：'2023-08-18 23:44:07.0000000'，还有一个时间段范围：05:00-05:30,12:00-13:30,17:00-17:30,23:00-23:30如何判断这个开始时间到结束时间是否包含这些时间段，如果包含，包含多少个呢

Answer 1

只能给你个例子你看看可以满足不

DECLARE @StartTime DATETIME = '2023-08-17 12:28:07.0000000'
DECLARE @EndTime DATETIME = '2023-08-18 23:44:07.0000000'

-- Convert start time and end time to TIME type
DECLARE @StartTimeOnly TIME = CAST(@StartTime AS TIME)
DECLARE @EndTimeOnly TIME = CAST(@EndTime AS TIME)

-- Define time ranges
DECLARE @TimeRanges TABLE (
    StartTime TIME,
    EndTime TIME
)

INSERT INTO @TimeRanges (StartTime, EndTime)
VALUES ('05:00', '05:30'),
       ('12:00', '13:30'),
       ('17:00', '17:30'),
       ('23:00', '23:30')

-- Count the number of overlapping time ranges
SELECT COUNT(*) AS OverlappingTimeRanges
FROM @TimeRanges
WHERE @StartTimeOnly <= EndTime AND @EndTimeOnly >= StartTime