如何在SQL中将分钟转换为HH：MM：SS？ [英] How to convert minutes into HH:MM:SS in SQL?

问题描述

我想计算员工的工作时间总和为HH：MM：SS和十进制的总天数。



这是我的查询..但是我没有得到总小时的实际总和：分钟：秒



我想要这样的输出。



工作时间总和=52:02:04，总天数= 6.5天（每天8小时）



什么我试过了：

I want to calculate sum of working hours of employee into HH:MM:SS and total numbers of days in decimal..

This is my Query...But I'm not getting the actual sum of total hours:minutes:seconds

I want the output like this.

sum of working hours ="52:02:04" and total days=6.5 days (per day 8 hours)

What I have tried:

select isnull(CONVERT(char(8), DATEADD(HH,SUM(DATEDIFF(MINUTE,FROMTIME,TOTIME)), ''), 114),'00:00:00') as 'TotalHours',isnull((sum(DATEDIFF(HH,FROMTIME,TOTIME))/8),'0') as 'TotalDay' FROM TRNSWRKPROGRESS 

推荐答案

最后我得到了解决方案..它运作良好...

Finally I got solution..It's working good...

select 
cast(sum(datediff(second,FROMTIME,TOTIME))/3600 as varchar(12)) + ':' + 
        right('0' + cast(sum(datediff(second,FROMTIME,TOTIME))/60%60 as varchar(2)),2) +
        ':' + right('0' + cast(sum(datediff(second,FROMTIME,TOTIME))%60 as varchar(2)),2) as 'Total Hours',
        CAST(sum(DATEDIFF(SECOND, FROMTIME, TOTIME)/28800.0) AS DECIMAL(5,2)) as 'Totaldays'
         from  TRNSWRKPROGRESS 

这篇关于如何在SQL中将分钟转换为HH：MM：SS？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！