create table test(ddate datetime)insert into test values('2010-01-11 12:00:00')
insert into test values('2010-01-12 13:10:35')
insert into test values('2010-01-13 08:30:00')
insert into test values('2010-01-14 10:15:50')
insert into test values('2010-01-06 16:20:13')
insert into test values('2010-01-10 16:20:13')
insert into test values('2010-01-10 10:15:25')
insert into test values('2010-01-12 13:10:35')
insert into test values('2010-01-13 08:30:00')
insert into test values('2010-01-14 10:15:50')
insert into test values('2010-01-06 16:20:13')
insert into test values('2010-01-10 16:20:13')
insert into test values('2010-01-10 10:15:25')
我现在要查询2010-1-5 17:30:50这个时间跟数据库中的2010-01-10 10:15:25这条数据相差的天数,小时数,分钟数,秒数,主要是要考虑到借位的问题,这条数据与要查询的这条数据相减后的结果应该是
天 小时 分钟 秒
4 16 44 35
sql server不熟,希望高手帮忙下。
if object_id('test') is not null drop table test
create table test(ddate datetime)
insert into test values('2010-01-11 12:00:00')
insert into test values('2010-01-12 13:10:35')
insert into test values('2010-01-13 08:30:00')
insert into test values('2010-01-14 10:15:50')
insert into test values('2010-01-06 16:20:13')
insert into test values('2010-01-10 16:20:13')
insert into test values('2010-01-10 10:15:25')select ddate,getdate() as [now],datediff(second,ddate,getdate())/(3600*24) as [day],
datediff(second,ddate,getdate()) % (3600*24) / 3600 as [hour],
datediff(second,ddate,getdate()) % 3600 / 60 as [minute],
datediff(second,ddate,getdate()) % 60 as [second]
from test/*
ddate now day hour minute second
----------------------- ----------------------- ----------- ----------- ----------- -----------
2010-01-11 12:00:00.000 2010-01-17 23:52:02.263 6 11 52 2
2010-01-12 13:10:35.000 2010-01-17 23:52:02.263 5 10 41 27
2010-01-13 08:30:00.000 2010-01-17 23:52:02.263 4 15 22 2
2010-01-14 10:15:50.000 2010-01-17 23:52:02.263 3 13 36 12
2010-01-06 16:20:13.000 2010-01-17 23:52:02.263 11 7 31 49
2010-01-10 16:20:13.000 2010-01-17 23:52:02.263 7 7 31 49
2010-01-10 10:15:25.000 2010-01-17 23:52:02.263 7 13 36 37
*/
insert into test values('2010-01-12 13:10:35')
insert into test values('2010-01-13 08:30:00')
insert into test values('2010-01-14 10:15:50')
insert into test values('2010-01-06 16:20:13')
insert into test values('2010-01-10 16:20:13')
insert into test values('2010-01-10 10:15:25')select
ddate,getdate() as 现在时间,
datediff(second,ddate,getdate())/(3600*24) as 天,
datediff(second,ddate,getdate()) % (3600*24) / 3600 as 小时,
datediff(second,ddate,getdate()) % 3600 / 60 as 分钟,
datediff(second,ddate,getdate()) % 60 as 秒
from
testdrop table test
/*ddate 现在时间 天 小时 分钟 秒
----------------------- ----------------------- ----------- ----------- ----------- -----------
2010-01-11 12:00:00.000 2010-01-17 23:58:37.803 6 11 58 37
2010-01-12 13:10:35.000 2010-01-17 23:58:37.803 5 10 48 2
2010-01-13 08:30:00.000 2010-01-17 23:58:37.803 4 15 28 37
2010-01-14 10:15:50.000 2010-01-17 23:58:37.803 3 13 42 47
2010-01-06 16:20:13.000 2010-01-17 23:58:37.803 11 7 38 24
2010-01-10 16:20:13.000 2010-01-17 23:58:37.803 7 7 38 24
2010-01-10 10:15:25.000 2010-01-17 23:58:37.803 7 13 43 12(7 行受影响)*/
create table test(ddate datetime) insert into test values('2010-01-11 12:00:00')
insert into test values('2010-01-12 13:10:35')
insert into test values('2010-01-13 08:30:00')
insert into test values('2010-01-14 10:15:50')
insert into test values('2010-01-06 16:20:13')
insert into test values('2010-01-10 16:20:13')
insert into test values('2010-01-10 10:15:25')
select ddate,datediff(ss,'2010-1-5 17:30:50',ddate)/(24*60*60) as d,0 as h,0 as m,0 as s into # from testupdate # set h=((datediff(ss,'2010-1-5 17:30:50',ddate)-d*24*60*60)/(60*60))
update # set m=((datediff(ss,'2010-1-5 17:30:50',ddate)-d*24*60*60-h*60*60)/60)
update # set s=(datediff(ss,'2010-1-5 17:30:50',ddate)-d*24*60*60-h*60*60-m*60)select ddate,d 天,h 小时,m 分钟,s 秒 from #
-------------------------------2010-01-11 12:00:00.000 5 18 29 10
2010-01-12 13:10:35.000 6 19 39 45
2010-01-13 08:30:00.000 7 14 59 10
2010-01-14 10:15:50.000 8 16 45 0
2010-01-06 16:20:13.000 0 22 49 23
2010-01-10 16:20:13.000 4 22 49 23
2010-01-10 10:15:25.000 4 16 44 35
原来用/就可以了,呵呵。
set @StartTime='2010-1-5 17:30:50'
declare @EndTime datetime
set @EndTime='2010-01-10 10:15:25'select datediff(second,@StartTime,@EndTime)/(3600*24) '天数',
datediff(second,@StartTime,@EndTime)%(3600*24)/3600 '小时',
datediff(second,@StartTime,@EndTime)%3600/60 '分钟',
datediff(second,@StartTime,@EndTime)%60 '秒'
天数 小时 分钟 秒
----------- ----------- ----------- -----------
4 16 44 35