22,210
社区成员
发帖
与我相关
我的任务
分享
declare @tb table(dt datetime)
insert into @tb values('2009-02-24 01:00:02')
insert into @tb values('2009-02-24 01:00:03')
insert into @tb values('2009-02-24 01:00:04')
insert into @tb values('2009-02-24 01:59:57')
insert into @tb values('2009-02-24 01:59:58')
insert into @tb values('2009-02-24 01:59:59')
insert into @tb values('2009-02-24 02:10:02')
insert into @tb values('2009-02-24 02:10:03')
insert into @tb values('2009-02-24 02:58:59')
insert into @tb values('2009-02-24 03:03:01')
insert into @tb values('2009-02-24 03:20:11')
select
top 1 b.dt
from
(select distinct convert(char(13),dt,120)+':00:00' as dt from @tb
union
select distinct convert(char(13),dateadd(hh,1,dt),120)+':00:00' from @tb) a,
@tb b
order by
abs(datediff(ss,a.dt,b.dt))
/*
dt
------------------------------------------------------
2009-02-24 01:59:59.000
*/
declare @tb table(dt datetime)
insert into @tb values('2009-02-24 01:00:01')
insert into @tb values('2009-02-24 01:00:02')
insert into @tb values('2009-02-24 01:00:03')
insert into @tb values('2009-02-24 02:10:01')
insert into @tb values('2009-02-24 02:10:03')
insert into @tb values('2009-02-24 03:20:11')
insert into @tb values('2009-02-24 03:03:01')
declare @Date datetime
set @Date ='2009-02-24 02:10:01.000'
select top 1 * from @tb
where dt <> @Date
order by abs(datediff(mi,dt,@Date))
结果:
dt
-----------------------
2009-02-24 02:10:03.000
declare @tb table(dt datetime)
insert into @tb values('2009-02-24 01:00:01')
insert into @tb values('2009-02-24 01:00:02')
insert into @tb values('2009-02-24 01:00:03')
insert into @tb values('2009-02-24 02:10:01')
insert into @tb values('2009-02-24 02:10:03')
insert into @tb values('2009-02-24 03:20:11')
insert into @tb values('2009-02-24 03:03:01')
declare @Date datetime
set @Date ='2009-02-24 02:10:01.000'
select top 1 * from @tb
order by abs(datediff(mi,dt,@Date))
结果:
dt
-----------------------
2009-02-24 02:10:01.000
create table tb(dt datetime)
insert into tb values('2009-02-24 01:00:01')
insert into tb values('2009-02-24 01:00:02')
insert into tb values('2009-02-24 01:00:03')
insert into tb values('2009-02-24 02:10:01')
insert into tb values('2009-02-24 02:10:03')
insert into tb values('2009-02-24 03:20:11')
insert into tb values('2009-02-24 03:03:01')
go
select m.* from tb m ,
(select convert(varchar(13),dt,120)+':00:00' dt, min(abs(datediff(ss , dt , convert(varchar(13),dt,120)+':00:00'))) ss from tb group by convert(varchar(13),dt,120)+':00:00') n
where convert(varchar(13),m.dt,120)+':00:00' = n.dt and abs(datediff(ss,m.dt,n.dt)) = n.ss
drop table tb
/*
dt
------------------------------------------------------
2009-02-24 01:00:01.000
2009-02-24 02:10:01.000
2009-02-24 03:03:01.000
(所影响的行数为 3 行)
*/