How to get range time in sql server

问题:

Basically what I want try to do is to get shift in range between

already get with this where

where @param_value_from_hour between From_Hour and To_Hour

it works when @param_value_from_hour let say 16:00 but what if I have to search @param_value_from_hour with 19:00?

回答1:

If the logic is that, if the end of the period is earlier than the start of the period then the period is assumed to span midnight (if it isn't, we need some other form of convention or things get hopelessly ambiguous):

where
   (@param_value_from_hour between From_Hour and To_Hour) or
   (To_Hour < From_Hour and
       (@param_value_from_hour < To_Hour or
        From_Hour < @param_value_from_hour)
   )

Should get approximately what you want (adjust < for <= if required)

回答2:

Convert your time columns to datetime and compare

declare @param_value_from_hour varchar(20) = '19:00'
;with myTable as (
    select * 
    from (
        values 
            (1, '7:00:00', '18:00:00')
            , (2, '18:00:00', '7:00:00')
    ) t(Shift, From_Hour, To_Hour)
)

select
    * 
from
    myTable
where
    cast('20180101 ' + @param_value_from_hour as datetime) between 
    cast('20180101 ' + From_Hour as datetime) 
    and cast('20180101 ' + To_Hour as datetime) + case when From_Hour < To_Hour then 1 else 0 end

回答3:

Try this:

CREATE TABLE Data (
    Shift INT,
    FromHour TIME,
    ToHour TIME
    );

INSERT INTO Data (Shift, FromHour, ToHour) VALUES
(1, '07:00:00', '18:00:00'),
(2, '18:00:00', '07:00:00');

DECLARE @Param TIME= '16:00:00'; --Change that to '19:00:00'

SELECT *
FROM Data
WHERE CAST(@Param AS DATETIME) BETWEEN 
      CAST(FromHour AS DATETIME)
        AND
    (CASE 
         WHEN (@Param >= '18:00:00' AND ToHour = '07:00:00') 
             THEN DATEADD(D, 1, CAST(ToHour AS DATETIME)) 
         ELSE CAST(ToHour AS DATETIME) 
    END)

回答4:

thank you for all your valuable respon

so this is the answer combine from 2 answer

declare @param_value_from_hour varchar(20) = '19:00'

;with myTable as (
    select * 
    from (
        values 
            (1, '07:00:00', '15:00:00')
            , (2, '15:00:00', '23:00:00')
            , (3, '23:00:00', '07:00:00')
    ) t(Shift, From_Hour, To_Hour)
)

select
    * 
from
    myTable
where
   (@param_value_from_hour between From_Hour and To_Hour) or
   (To_Hour < From_Hour and
       (@param_value_from_hour < To_Hour or
        From_Hour < @param_value_from_hour)
   )

thank you guys, you're awesome!!