在sql中将分钟转换为小时:分钟:秒 [英] converting minutes to hour:minutes:second in sql

问题描述

我有一个这样的表transaction:

transactID    Paydate                   DelDate                     vtid
24            2013-05-08 16:53:03.000   2013-05-08 17:00:28.000     8
25            2013-05-08 16:34:38.000   2013-05-08 17:00:14.000     7

我写了这样的查询来获取日期差异的总和:

I wrote the query like this for getting sum of date difference:

select 
    v.Vtype, 
    SUM(DATEDIFF(MI, t.Paydate, t.DelDate)) as sum_min,
    AVG(CONVERT(NUMERIC(18, 2), DATEDIFF(MI, t.Paydate, t.DelDate))) as avg_min
from 
    Transaction_tbl t 
left join 
    VType_tbl v on t.vtid = v.vtid 
where 
    t.transactID in (24, 25) 
group by 
    v.Vtype

我在几分钟内得到了正确的输出:

I am getting proper output in minutes:

Vtype           sum_min     avg_min
----- ----------- ---------------------------------------
Normal             26          26.000000
VIP                7           7.000000 

我想在 hh:mm:ss 中获得输出，而不是在几分钟内获得 sum_min 列.所以我的预期输出应该是这样的:

Instead of getting sum_min column in minutes I want to get output in hh:mm:ss. So my expected output should be like this:

Vtype           sum_min              avg_min
----- ----------- ---------------------------------------
Normal            00:26:00          26.000000
VIP                00:7:00           7.000000

为了得到这个，我写了这样的查询:

For getting this I wrote the query like this:

 SELECT 
     convert(varchar(10), sum(DATEDIFF(hour, t.Paydate, t.DelDate))) + ':' +
     convert(varchar(10), sum(DATEDIFF(minute, t.Paydate, t.DelDate) % 60)) + ':' +
     convert(varchar(10), sum(DATEDIFF(SECOND, t.Paydate, t.DelDate) % 60)) AS 'HH:MM:SS'
FROM 
     Transaction_tbl t 
WHERE 
     t.transactID in (24, 25) 
group by 
     vtid 

但在我的输出中 1 小时是额外的.在执行此查询时，我得到如下输出:

But in my output 1 hr is coming extra. While executing this query I am getting output like this:

 HH:MM:SS
--------------------------------
1:26:36
1:7:25

那么如何重写查询以获得正确的结果?

So how can I re-write my query for getting proper result?

推荐答案

如果您想将分钟或秒转换为 hh:mm:ss 格式，那么您可以使用这些函数 (DATEADD,CONVERT with样式 114 和左):

If you want to convert minutes or seconds to hh:mm:ss format then you could use these functions (DATEADD,CONVERT with style 114 and LEFT):

DECLARE @PayDate     DATETIME,
        @DelDate     DATETIME,
        @DiffSeconds INT,
        @DiffMinutes INT;

SELECT  @PayDate='2013-05-08T16:53:03.000',
        @DelDate='2013-05-08T17:00:28.000',
        @DiffSeconds=DATEDIFF(SECOND,@PayDate,@DelDate),
        @DiffMinutes=DATEDIFF(MINUTE,@PayDate,@DelDate);


SELECT  @DiffSeconds AS Diff_Seconds,
        LEFT(CONVERT(VARCHAR(50),DATEADD(SECOND,@DiffSeconds,0),114),8) AS Diff_From_Seconds,
        @DiffMinutes AS Diff_Minutes,
        LEFT(CONVERT(VARCHAR(50),DATEADD(MINUTE,@DiffMinutes,0),114),8) AS Diff_From_Minutes;

结果:

Diff_Seconds Diff_From_Seconds Diff_Minutes Diff_From_Minutes
------------ ----------------- ------------ -----------------
445          00:07:25          7            00:07:00

这篇关于在sql中将分钟转换为小时:分钟:秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！