与此问题有关
如何获得SQL Server中的范围时间
当我的输入值为 18:00
时,我想获得Shift 2此处从上述帖子中示例:
declare @param_value_from_hour varchar(20) = '18: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
(@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)
)
但是,有了这个解决方案,我才得到Shift 1,任何人都可以帮助我在这里错过的东西,谢谢
WHERE
(
@param_value_from_hour >= From_Hour
AND @param_value_from_hour < To_Hour
)
OR
(
From_Hour > To_Hour
AND
(
@param_value_from_hour >= From_Hour
OR @param_value_from_hour < To_Hour
)
)
poli,您是否必须仅将mytable作为CTE?问题是"时间"列的数据类型。.请检查临时表/类似方法是否可以提及数据类型适用于您
declare @param_value_from_hour varchar(20) = '18:00'
declare @myTable Table (Shift int, From_Hour time, To_Hour time)
insert into @myTable (Shift, From_Hour, To_Hour) values (1, '7:00:00', '18:00:00')
insert into @myTable (Shift, From_Hour, To_Hour) values (2, '18:00:00', '7:00:00')
Select * from @myTable
where
(@param_value_from_hour >= From_Hour and @param_value_from_hour < To_Hour) or
(To_Hour < From_Hour and
(@param_value_from_hour < To_Hour or
From_Hour <= @param_value_from_hour)
)