Home > DeveloperSection > Forums > How To Calculate Time Format Data In Sql?
Hugh Jackman
Hugh Jackman

Total Post:52

Points:366
Posted on    October-20-2014 11:39 PM

 MSSQL Server Database  MSSQL Server  SQL Server 2012 
Ratings:


 1 Reply(s)
 534  View(s)
Rate this:
My Table Below Record Overtime_HHMM column only sum Total time in (time format)


Table Structure is 
Overtime_HHMM  time(7)

ProcessDate    Overtime_HHMM
04 Mar 2014    02:59:00.0000000
05 Mar 2014    01:58:00.0000000
06 Mar 2014    01:18:00.0000000
07 Mar 2014    04:08:00.0000000
12 Mar 2014    00:39:00.0000000
13 Mar 2014    00:22:00.0000000
17 Mar 2014    02:01:00.0000000
22 Mar 2014    00:36:00.0000000
I want This Overtime_HHMM total is :14:01:00:0000000

My Query is
select sum(datediff(minute,'0:00:00',CONVERT(time,Overtime_HHMM)))/60.0 as TotalHoursWorked from Accounts_DailyAttendance where UserID='1050' and datename(month,processdate) ='March' and datepart(yyyy,processdate)= '2014' and LateIn > 5


Brad Pitt
Brad Pitt

Total Post:61

Points:429
Posted on    October-20-2014 11:40 PM

Something like

select concat(cast(sum(datediff(minute,'0:00:00',CONVERT(time,Overtime_HHMM)))/60.0 as int), ':', cast(sum(datediff(minute,'0:00:00',CONVERT(time,Overtime_HHMM)))%60.0 as int))  as TotalHoursWorked
from Accounts_DailyAttendance where datename(month,processdate) ='March' and datepart(yyyy,processdate)= '2014'

You might be able to make it a little more intelligent and add the seconds component as well.

Don't want to miss updates? Please click the below button!

Follow MindStick