CODEWITHSUNDEEP
sql-serverdatabasedatetimedatetime-format
mathewtinus
mathewtinus

Reputation: 49

Sum of datetime column in sql 05

I have a column named as "total_hours_worked" in my sql table which is of "datetime" datatype I want to find out the total of "total hours worked in sql server".

How to do this? I googled but didn't got a practical solution.

Upvotes: 0

Views: 5194

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13506

Try this.Here I considered seconds also.

declare @T table
(
  total_hours_worked datetime
)

insert into @T values ('05:30:00')
insert into @T values ('10:00:00')
insert into @T values ('15:00:00')
insert into @T values ('05:25:45')

select SUM((DATEPART(hh,total_hours_worked)*60)+DATEPART(mi,total_hours_worked)+(DATEPART(ss,total_hours_worked)/(60.0)))/60.0  as TotalHours from @T

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Something like this if I understand your data correctly.

declare @T table
(
  total_hours_worked datetime
)

insert into @T values ('05:30:00')
insert into @T values ('10:00:00')
insert into @T values ('15:00:00')

select sum(datediff(minute, 0, total_hours_worked)) / 60.0 as hours_worked
from @T

Result:

hours_worked
---------------------------------------
30.500000

If you only need to store the hours you should consider an integer datatype instead of datetime. It will be more efficient and easier to deal with.

Upvotes: 1

Related Questions