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

Question

0.00/5 (No votes)

See more:

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.

What I have tried:

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

Posted 19-Sep-16 19:36pm

KUMPREK

Updated 20-Sep-16 2:07am

Add a Solution

Comments

Patrice T 20-Sep-16 1:49am

Rather than showing an example that works, you should show an example that don't work.

KUMPREK 20-Sep-16 1:54am

The first code snippet show the not working code.

[no name] 20-Sep-16 3:19am

What's you question?
Send the query that you try and the expected result.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KUMPREK · Accepted Answer · 2016-09-20T02:07:00

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:

SQL

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))