开发者

Comparing Time in SQL Server 2008

开发者 https://www.devze.com 2023-01-21 20:16 出处:网络
Here is my table. I need a query which returns the shift id for a specified time How can I get the value?

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

0

精彩评论

暂无评论...
验证码 换一张
取 消