sql - 日期时差得到小时:minutes:seconds

Question

我有这样的查询:

select t.Paydate,t.DelDate,DATEDIFF(MI, t.Paydate,t.DelDate) as Datdiffernce 
from Transaction_tbl t 
where t.transactID=19

我是这样出去的:

Paydate                 DelDate                 Datdiffernce
----------------------- ----------------------- ------------
2013-05-07 18:36:50.000 2013-05-07 18:58:32.000     22

Datedifference 我只得到 22 分钟。

而不是获取这种格式的日期差异hh:mm:ss？

Answer 1

试试这个，

SELECT convert(varchar(10),DATEDIFF(hour,t.Paydate,t.DelDate))+'hr:' 
      +convert(varchar(10),DATEDIFF(minute,t.Paydate,t.DelDate)% 60) + 'mnts:' 
      +convert(varchar(10),DATEDIFF(SECOND,t.Paydate,t.DelDate)% 60) +'seconds'
       AS 'DIFF IN HH:MM:SS'
FROM Transaction_tbl t 
WHERE t.transactID=19