如何在SQL中获得最近的时间? [英] How to get nearest time in SQL?
问题描述
大家好,
我想从给定的桌子到最近的时间
我的桌子
< pre lang =SQL> 声明 @ Mytable 表
(
Shift nvarchar ( 20 ),
STtime 时间
)
insert 进入 @ Mytable 值(' 05 - 14',' 05:00: 00.000')
插入 进入 @Mytable 值(' 06 - 15' 跨度>,<跨度class =code-string>' 06:00:00.000')
insert 进入 @ Mytable 值( ' 07 - 16',' < span class =code-string> 07:00:00.000')
insert into @ Mytable 值(' 08 - 17',' 08:00:00.000 ')
插入 进入 @Mytable 值(' 09 - 18 ',' 09:00:00.000')
insert 进入 @ Mytable 值(' 10 - 19',' 10:00:00.000')
insert into @ Mytable 值(' 11 - 20',' 11:00:00.000')
insert into @ Mytable 值(' 12 - 21', ' 12:00:00.000')
insert into @ Mytable values (' 13 - 22',' 13:00:00.000')
插入 进入 @ Mytable 值(' 14 - 23',' 14:00:00.000')
插入 进入 @ Mytable values (' 15 - 00','跨度> 15:00:00.000')
insert into @ Mytable 值(' 16 - 01',' 16:00:00.000 ')
插入 进入 @Mytable 值(' 17 - 02 ',' 17:00:00.000')
插入 进入 @ Mytable 值(' 18 - 03',' 18:00:00.000')
insert into @ Mytable 值(' 19 - 04',' 19:00:00.000')
如果我通过日期2016-03-16 07:58:56.400然后我想从上面08 - 09记录桌子
我尝试过:
select top 1 * from @Mytable
由ABS订购(DATEDIFF(分钟,STtime,'2016-03-16 07:58:56.400'))
i尝试了这个,但无法正常工作
你在比较日期时间与时间的关系,显然效果不佳。我建议你首先提取该日期时间的时间部分,然后尝试选择转移:
DECLARE @ datetime DATETIME = ' 2016-03-16 07:58:56.400'
DECLARE @time TIME
SET @time = @ datetime - CAST( @ datetime AS DATE )
select * < span class =code-keyword> from @ Mytable
order < span class =code-keyword> by ABS(DATEDIFF(分钟,STtime,@ time ))
Hello every one,
I want the nearest time from given table
my table
declare @Mytable Table
(
Shift nvarchar(20),
STtime time
)
insert into @Mytable values('05 - 14','05:00:00.000')
insert into @Mytable values('06 - 15','06:00:00.000')
insert into @Mytable values('07 - 16','07:00:00.000')
insert into @Mytable values('08 - 17','08:00:00.000')
insert into @Mytable values('09 - 18','09:00:00.000')
insert into @Mytable values('10 - 19','10:00:00.000')
insert into @Mytable values('11 - 20','11:00:00.000')
insert into @Mytable values('12 - 21','12:00:00.000')
insert into @Mytable values('13 - 22','13:00:00.000')
insert into @Mytable values('14 - 23','14:00:00.000')
insert into @Mytable values('15 - 00','15:00:00.000')
insert into @Mytable values('16 - 01','16:00:00.000')
insert into @Mytable values('17 - 02','17:00:00.000')
insert into @Mytable values('18 - 03','18:00:00.000')
insert into @Mytable values('19 - 04','19:00:00.000')
if I pass the date 2016-03-16 07:58:56.400 then i want 08 - 09 record from above table
What I have tried:
select top 1 * from @Mytable
order by ABS(DATEDIFF(minute, STtime ,'2016-03-16 07:58:56.400' ))
i tried this but can't work
What you are comparing there is datetime vs. time and it apparently doesn't work well. I suggest you first extract the time part of that datetime and only then try to selef the shift:
DECLARE @datetime DATETIME = '2016-03-16 07:58:56.400' DECLARE @time TIME SET @time = @datetime - CAST(@datetime AS DATE) select * from @Mytable order by ABS(DATEDIFF(minute, STtime , @time))
这篇关于如何在SQL中获得最近的时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!