在hr中计算工作持续时间的总和：m：ss [英] Calculate the sum of work duration in hr:m:ss in SQL

问题描述

大家好，



我需要计算工作时间的总和。但是我没有得到正确的持续时间。可以请任何人帮助我。任何帮助都将是非常感谢。



这是我的工作时间：

Hi all,

I need to calculate sum of work duration .However I am not getting the correct duration .Could anyone help me please .Any help will be really appreciated .

This is my work duration:

worduration
8:30:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
9:00:00
8:00:00
9:00:00
9:00:00
9:00:00
1:01:21

我尝试过：

What I have tried:

convert(char(8),dateadd(second,SUM ( DATEPART(hh,(convert(datetime,worduration,1))) * 3600 + DATEPART(mi, (convert(datetime,worduration,1))) * 60 + DATEPART(ss,(convert(datetime,worduration,1)))),0),108) as totalworkdur

这里我的工作时间为：

Here I am getting my work duration as :

08:31:21

推荐答案

将您的工作时间存储为总计s econds，并将其处理为hh：mm：ss仅用于演示。它使得数学比将其强制转换为完全不合适的DateTime容易得多。

DateTime是一个标记点，它包含自过去一个固定点以来的多个刻度，而不是持续时间 - 您需要的是SQL不支持的TimeSpan。 br />


将它存储为秒可让您轻松将它们相加，然后一个简单的SQL函数将其转换为表示字符串：

Store your work duration as a total seconds, and post process it to hh:mm:ss for presentation only. It makes the math easier a lot easier than forcing it into a DateTime which is wholly inappropriate.
A DateTime is a "marked point" in time which consists of a number of ticks since a fixed point in the past, not a duration - what you need is a TimeSpan which SQL does not support.

Storing it as seconds lets you sum them easily, and then a simple SQL Function will convert it to a presentation string:

CREATE FUNCTION [dbo].[ConvertSecondsToHHMMSS]
                (@Seconds INT)
RETURNS         NVARCHAR(20)
AS
BEGIN
    DECLARE @hh INT;
    DECLARE @mm INT;
    DECLARE @ss INT;

    SET @hh = @seconds /60 / 60;
    SET @mm = (@seconds / 60) - (@hh * 60 );
    SET @ss = @seconds % 60;
    RETURN CONVERT(NVARCHAR(9), @hh) + ':' + 
           RIGHT('00' + CONVERT(NVARCHAR(2), @mm), 2) + ':' +
           RIGHT('00' + CONVERT(NVARCHAR(2), @ss), 2)
END

这篇关于在hr中计算工作持续时间的总和：m：ss的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！