Time Comparison in SQL Server 2008

Question

Here is my table. I need a query that returns a shift identifier within a specified time.
How can I get a value?

shift_ID   shift_From                shift_To
1          2010-09-21 10:00:00.000   2010-09-21 18:10:00.000
2          2010-09-21 20:00:00.000   2010-09-21 05:00:00.000

Suppose I give 02:00:00 as input. I need to get the shift identifier as 1. How can I do this?

Answer 1

Try:

SELECT shift_ID
FROM time_shift
WHERE
DATEDIFF(hour, shift_From, shift_To) = 2 -- your input

Learn more about DATEDIFF on MSDN

The first argument is the temporary part that you specify DATETIFF (hour, minute, second).

If your input matches exactly 02:00:00, you need to parse it to determine what is specified as the first argument.

---

, , :

SELECT shift_ID
FROM time_shift
WHERE
    CAST(shift_From AS TIME) < CAST(@input AS TIME)
AND
    CAST(@input AS TIME) < CAST(shift_To AS TIME)
-- you can specify operators inclusiveness, i.e. <= >= etc
-- or
CAST(@input AS TIME) BETWEEN (CAST(shift_From AS TIME), CAST(shift_To AS TIME))

MSDN