Select Datediff(ss,min(dd),max(dd)) as [时间差(秒)] from (Select top 2 时间字段 as dd from 表 order by dd desc) t
CREATE TABLE TEST(id INT IDENTITY(1,1),times DATETIME) INSERT TEST(TIMES) SELECT '2006-8-31 15:15:15' UNION ALL SELECT '2006-8-31 15:15:19' UNION ALL SELECT '2006-8-31 15:15:29' UNION ALL SELECT '2006-8-31 15:15:38' SELECT * FROM TESTSELECT TOP 1 DATEDIFF(S,(SELECT TOP 1 TIMES FROM TEST WHERE ID<A.ID),TIMES) FROM TEST A ORDER BY ID DESC DROP TABLE TEST
select datediff(ss,a.time,b.time) from (select top 2 id,time from tb order by time desc) a join (select top 2 id,time from tb order by time desc) b on a.time>b.time
DATEDIFF 返回跨两个指定日期的日期和时间边界数。 语法 DATEDIFF ( datepart , startdate , enddate ) 参数 datepart是规定了应在日期的哪一部分计算差额的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。日期部分 缩写 year yy, yyyy quarter qq, q Month mm, m dayofyear dy, y Day dd, d Week wk, ww Hour hh minute mi, n second ss, s millisecond ms
select datediff(ss,(select top 1 时间字段 from (select top 2 * from 表 order by id字段 desc)a order by a.id字段 asc),(select top 1 时间字段 from 表 order by id字段 desc)) 如果结果为正,说明最后一条记录时间晚于最后第二条.否则反之
from (Select top 2 时间字段 as dd
from 表 order by dd desc) t
INSERT TEST(TIMES)
SELECT '2006-8-31 15:15:15' UNION ALL
SELECT '2006-8-31 15:15:19' UNION ALL
SELECT '2006-8-31 15:15:29' UNION ALL
SELECT '2006-8-31 15:15:38' SELECT * FROM TESTSELECT TOP 1 DATEDIFF(S,(SELECT TOP 1 TIMES FROM TEST WHERE ID<A.ID),TIMES) FROM TEST A
ORDER BY ID DESC DROP TABLE TEST
from
(select top 2 id,time
from tb
order by time desc) a
join
(select top 2 id,time
from tb
order by time desc) b
on a.time>b.time
返回跨两个指定日期的日期和时间边界数。 语法
DATEDIFF ( datepart , startdate , enddate ) 参数
datepart是规定了应在日期的哪一部分计算差额的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。日期部分 缩写
year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
如果结果为正,说明最后一条记录时间晚于最后第二条.否则反之