14,599,199 members
Rate this:
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

Rate this:

## 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```