# Thread: Sum Time in sql server

1. Registered User
Join Date
Feb 2005
Location
Ferrara (Italy)
Posts
11

## Unanswered: Sum Time in sql server

Ciao,
I'm a beginner programmer and I am in trouble about the sum of a time field.
This is may problem:
I woud like the sum the time of datetime column and get tha amount

Example:

08:00 + 12:00 + 05:00 + 17:00 = 42 Hours

Thank you very much

Davide

2. Registered User
Join Date
Jan 2005
Location
TempDb
Posts
228
You can do this with DateDiff(), however, you need to ensure your dates are valid. If you are not storing relevant dates, you need to manipulate your values to ensure they have the correct date portion. That will require a sequential value as a reference.

Post sample data if you need more help. I was unable to figure out how you came to 42 (which, coincidentally(?), is the answer to THE QUESTION - see Hitchhikers Guide to the Galaxy for THE QUESTION):
Select DateDiff(hh, '01/01/2005 08:00', '01/01/2005 12:00')
, DateDiff(hh, '01/01/2005 12:00', '01/02/2005 05:00')
, DateDiff(hh, '01/02/2005 05:00', '01/02/2005 17:00')
, DateDiff(hh, '01/01/2005 08:00', '01/02/2005 00:00')
+ DateDiff(hh, '01/02/2005 00:00', '01/02/2005 05:00')
+ DateDiff(hh, '01/02/2005 05:00', '01/02/2005 17:00')

3. Registered User
Join Date
Feb 2005
Location
Ferrara (Italy)
Posts
11
Dear Maxa,
but unfortunately explained in bad way my request, I need the amount of a whole datetime column.
example:

Select sum (time1) as Amount time (Doesn't work)
from Times
where startday = 01012005 and endday = 31012005

The problem is that the SUM function doesn't work with datetime field
I need to calculate the total of datetime column.

I'm studying and I understood that I should dinamically convert the datetime value in float for each record, after make the SUM of this float field for each record and at the end to convert the float value in datetime.
can you help me??

Thank you / Grazie

Davide

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
what are the date values that you use for those times?

you could try this -- extract the times, convert them to seconds, sum the seconds, then convert the seconds back to a time

but this will be tricky because you must always have a date involved in the calculations

can you not change the datatype of the column to something more reasonable, like an integer (e.g. to represent seconds)?

5. Registered User
Join Date
Feb 2005
Location
Ferrara (Italy)
Posts
11

## Thank you Rudy

Thank you Rudy for your help,
I'm working about a program to calculate the amount of time work of personnel in my office.
This is a real example of my table:

PersonalID(Char4) | Surname(Varchar 40)| Name(Varchar 40) | TimeIn(Datetime) | TimeOut(DateTime) | TotalTime(DateTime)

The Field TotalTime is calculated: TimeOut - TimeIn

Now I need to Sum the column TotalTime because my chief Has to pay exactly the time spent to work.
I cannot change the structure of the table because it's property of a program to read the badges.

Thank you again

Davide

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
can you do me a favour, run this query e to display a few values:
Code:
```select top 10
PersonalID
, convert(char(20),TimeIn,120) as i
, convert(char(20),TimeOut,120) as o
, convert(char(20),TotalTime,120) as t
from Times
order
by 3 desc```

7. Registered User
Join Date
Feb 2005
Location
Ferrara (Italy)
Posts
11

## I'll try

Dear Rudy,
thank you again, tomorrow I'll try.
Reading the newsgroup and studying on the manual I would like to try something like this:

Select sum(datepart(hour,convert(datetime,TotalTime))) from AS TotalHours
from MyTable -- Sum Hours

Select sum(datepart(minute,convert(datetime,TotalTime))) AS TotalMinutes
from MyTable -- Sum Minutes

OR another solution could be:

Select Sum(DateDiff("hh", TimeOut, TimeIn)) As TotalHours
from MyTable -- Sum Hours

Select Sum(DateDiff("mm", TimeOut, TimeIn)) As TotalMinutes
from MyTable -- Sum Minutes

Do you think that It could work?

Thank you again

Davide

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
did you try the query i suggested? i would like to see the results

yes, your SUM of DATEDIFFs should work, except the earlier time has to go first

in the DATEPARTs you use CONVERT, and that should not be necessary

9. Registered User
Join Date
Feb 2005
Location
Ferrara (Italy)
Posts
11

## I tested

Ciao,
I sorry for delay but for mr was impossible to go to work and test your code because I was sick, now is better I went to office to work.
Here there is the code that you suggested me.

select top 10
PersonalID
, convert(char(20),TimeIn,120) as i
, convert(char(20),TimeOut,120) as o
, convert(char(20),TotalTime,120) as t
from Times
order
by 3 desc

Well:
, convert(char(20),TimeIn,120) as i
, convert(char(20),TimeOut,120) as o
returns the date and time corretly! Very good
The problem is with
, convert(char(20),TotalTime,120) as t
it returns NULL!!!!!!
Really NULL, because this column is a calculated field of delphi program.
If I open the table with a normal query I can see exactly the values, but with your procedure I see only NULL.
Infact using the formula of sum of datepart of my formula with the column TotalTime I have strange values, I tried with the columns TimeIn and TimeOut and the sum of the values is correct.
The problem is with the column TotalTime, strange, very strange, I can see the values with queries, but I can't convert it.
Another question, I would like to convert the type from datetime to smalldatetime and I encountered an error taht is not possible to convert the type. I can I convert the type?

Thank you very much!!

Ciao

Davide

10. Registered User
Join Date
Jan 2005
Location
TempDb
Posts
228
Provide DDL and sample data. However, the solution would seem to be:
select top 10 PersonalID
, convert(char(20),TimeIn,120) as i
, convert(char(20),TimeOut,120) as o
, convert(char(20),DateDiff(hh, TimeIn, TimeOut)) as t
from Times
order
by 3 desc
TotalTime needs to be expressed in terms of milliseconds or seconds or minutes or hours or a combination or etc. to be meaningful.

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
nice catch, maxA -- your query at least provides an intermediate answer

davidus, make sure you understand maxA's advice:
TotalTime needs to be expressed in terms of milliseconds or seconds or minutes or hours or a combination or etc. to be meaningful.
if you have an external program that is calculating the difference on each row, then it should store that difference not as a datetime, but as an integer -- i recommend seconds

then you can sum it easily

that was the final objective, to add up a bunch of times, right?

if you have external program logic that calculates the difference already, then don't repeat that in your query

on the other hand, if you did decide to do it in the query, you wouldn't need to store the TotalTime field at all

12. Registered User
Join Date
Feb 2005
Location
Ferrara (Italy)
Posts
11

## Thank you, but...

Thank you Maxa and Rudy,
Probably the problem is in my delphi source code, because the totalTime column was managed as time field type and not as datetime type, I'll modify the source and TotalTime field is (TimeOut - TimeIn) + (timeOut1 - TimeIn1) - LunchTime.
I prefer to obtain this calculation field throught Delphi source
Tomorrow I'll try and I'll let you know.

Tahnk you very much

Davide

13. Registered User
Join Date
Dec 2009
Posts
3

## sum(totalhrs workedcolumn)

Hi David
I know this is an old post but i would be greatful if you could tell me how to calculate the sum of column with values
totwrkhrs
12:00
08:00
06:00

i require the sum of totwrkhrs column
regards
oracleuser

Originally Posted by davidus
Thank you Maxa and Rudy,
Probably the problem is in my delphi source code, because the totalTime column was managed as time field type and not as datetime type, I'll modify the source and TotalTime field is (TimeOut - TimeIn) + (timeOut1 - TimeIn1) - LunchTime.
I prefer to obtain this calculation field throught Delphi source
Tomorrow I'll try and I'll let you know.

Tahnk you very much

Davide

14. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
```DECLARE @x table (
totwrkhrs datetime
)

INSERT INTO @x (totwrkhrs) VALUES ('12:00')
INSERT INTO @x (totwrkhrs) VALUES ('08:00')
INSERT INTO @x (totwrkhrs) VALUES ('06:00')
INSERT INTO @x (totwrkhrs) VALUES ('06:12')

SELECT totwrkhrs
FROM   @x

SELECT DateDiff(mi, 0, totwrkhrs) As time_in_minutes
FROM   @x

SELECT Sum(DateDiff(mi, 0, totwrkhrs)) As total_minutes
FROM   @x

SELECT total_minutes
, total_minutes / 60 As hours
, total_minutes &#37; 60 As mins
FROM   (
SELECT Sum(DateDiff(mi, 0, totwrkhrs)) As total_minutes
FROM   @x
) As derived_table

FROM   (
SELECT total_minutes / 60 As hours
, total_minutes % 60 As mins
FROM   (
SELECT Sum(DateDiff(mi, 0, totwrkhrs)) As total_minutes
FROM   @x
) As derived_table
) As another_derived_table```
Any good for ya?

15. Registered User
Join Date
Dec 2009
Posts
3
Hi all
I am giving a piece of my code

SELECT dif.EMPLOYEE_NUMBER Employee#, dif.FULL_name EmployeeName,
TO_CHAR(START_DATE,'dd-Mon-rrrr') DOJ ,dif.DEPT_NAME,POSITION , DATE_ENTRAY AttendanceDate,

to_char(DATE_ENTRAY,'DY') day, TO_CHAR(LNE1,'hh24:mi')TimeIn1 ,TO_CHAR(LNE2,'hh24:mi')TimeOut1,

TO_CHAR(LNE3,'hh24:mi')TimeIn2 ,TO_CHAR(LNE4,'hh24:mi')TimeOut2, TO_CHAR(LNE5,'hh24:mi')TimeIn3 ,

TO_CHAR(LNE6,'hh24:mi')TimeOut3, to_char(decode(LNE8,null ,

decode(LNE7,null, decode(LNE6,null, decode(LNE5,null, decode(LNE4,null, decode(LNE3,null,

decode(LNE2,null,LNE2 ,LNE2 ),LNE3 ),LNE4 ),LNE5 ),LNE6 ),LNE7 ),LNE8 ),'hh24:mi')TimeOuts ,

ACT_HOUR Work_Hrs,

decode(DLY_ABSENT_TYPE,'Late',DED_ABS ,'Late (Deduction)',DED_ABS,'00:00') Late_Hrs,ACT_OVT Over_Time
FROM jjj_PUNCH_DATA_EMP_LIST trn ,
jjj_emp_def dif,jjj_PUNCH_CARD_ELEG ele WHERE trn.EMPLOYEE_NUMBER =dif.EMPLOYEE_NUMBER and
ele.EMPLOYEE_NUMBER =trn.EMPLOYEE_NUMBER and DATE_ENTRAY between '23-Aug-2009' and '24-Aug-2009'

I require to find the sum of column wrk_hrs
Act_hour or wrk_hrs belongs to table jjj_PUNCH_DATA_EMP_LIST trn , and the datatype of act_hour is varchar(10 byte)
the values of column act_hrs, (i require the sum of this column)
08:00
07:22
06:08

kindly help