How do I query time data type with between 2 times varying over midnight

How do I query time data type with between 2 times varying over midnight. Here is what I have tried.

declare @timeValue time
SET @timeValue = '23:30:00'

SELECT @timeValue,DATEADD(minute, -30, @timeValue),DATEADD(minute, +30, @timeValue)
WHERE @timeValue BETWEEN DATEADD(minute, -30, @timeValue) AND DATEADD(minute, +30, @timeValue)

But if we take the above example for finding if the time is valid in between 30 mins before and after, and it it varies over midnight. I don’t get expected result.

Expected result would be the row with value in timeValue variable along with 30 mins before and after as other columns.

But if I use the time which is not crossing over the midnight like the one below it works properly.

declare @timeValue time
SET @timeValue = '23:00:00'

SELECT @timeValue,DATEADD(minute, -30, @timeValue),DATEADD(minute, +30, @timeValue)
WHERE @timeValue BETWEEN DATEADD(minute, -30, @timeValue) AND DATEADD(minute, +30, @timeValue)

And I get the following response as expected.

---

23:00:00.0000000 22:30:00.0000000 23:30:00.0000000

Answer

Your time wraps at midnight. In other words: 23:30 + 00:30 = 00:00 which is less than 23:30. Condition 23:00 < 23:30 < 00:00 is not met. Workaround is to convert arguments to datetime:

declare @timeValue time
SET @timeValue = '23:30:00'

SELECT @timeValue,DATEADD(minute, -30, @timeValue),DATEADD(minute, +30, @timeValue)
WHERE CONVERT(datetime,@timeValue) BETWEEN DATEADD(minute, -30, CONVERT(datetime,@timeValue)) AND DATEADD(minute, +30, CONVERT(datetime,@timeValue))