Click here to Skip to main content
14,599,199 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 20-Mar-18 2:44am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100