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 2开发者_JS百科010-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?
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
精彩评论