sql sever时间范围问题

Question

在sql sever中有一个开始日期，一个结束日期，格式为：2023-09-12 00:12:02.0000000；还有一个自定义的时间范围，05:00到05:30；12:00到13:30；17：00到17:30；23:00到23:30；请问如何判断开始日期到结束日期是否在自定义的范围内，如果在的话，是在哪个范围内呢，并且如果在时间范围内，显示是开始时间在范围内还是结束时间在范围内，或者两者都在

Answer 1

看一下这个，记得采纳哦
以通过SQL Server中的日期和时间函数来解决

SELECT   
    CASE   
        WHEN (DATEPART(HOUR, start_date) BETWEEN 5 AND 5.5) OR (DATEPART(HOUR, end_date) BETWEEN 5 AND 5.5) THEN '05:00到05:30'  
        WHEN (DATEPART(HOUR, start_date) BETWEEN 12 AND 13.5) OR (DATEPART(HOUR, end_date) BETWEEN 12 AND 13.5) THEN '12:00到13:30'  
        WHEN (DATEPART(HOUR, start_date) BETWEEN 17 AND 17.5) OR (DATEPART(HOUR, end_date) BETWEEN 17 AND 17.5) THEN '17:00到17:30'  
        WHEN (DATEPART(HOUR, start_date) BETWEEN 23 AND 23.5) OR (DATEPART(HOUR, end_date) BETWEEN 23 AND 23.5) THEN '23:00到23:30'  
        ELSE '不在自定义范围内'  
    END AS time_range  
FROM your_table