Comparing Time in SQL Server 2008

Question

Here is my table. I need a query which returns the shift id for a specified time
How can I get the 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 am giving 02:00:00 as input I need to get the shift ID 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

See more about DATEDIFF on MSDN

The first argument is the time part you're specifying to DATETIFF (hour, minute, second).

If your input is strictly like 02:00:00 you need to parse it to determine what specify as the first argument.

---

To determine does the specified date belong between 2 others, use:

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

See more about TIME on MSDN