CODEWITHSUNDEEP
sqlsql-serverdatabase
Sas
Sas

Reputation: 2503

How to sum up time field in SQL Server

I have a column called "WrkHrs" and the data type is time(hh:mm:ss). I want to sum up the working hours for employees. But since it's time data type sql server doesn't let me use like sum(columnname).

How can I sum up the time data type fieled in sql query?

Upvotes: 38

Views: 115996

Answers (6)

sesu raj
sesu raj

Reputation: 1

It must be as simple as that.

Steps

  • convert time to seconds
  • sum the RESULT
  • convert the sum to time Eg: take a case you might want to sum the following time:
| present_hours   |
|-----------------|
| 00:01:20.000000 |
|-----------------|
| 00:01:13.000000 |
|-----------------|
| 00:01:45.000000 |
|-----------------|
| 00:01:03.000000 |
|-----------------|
| 00:01:10.000000 |
|-----------------|
| 00:00:56.000000 |

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(present_hours))) as total_present_hours FROM time_booking;

Upvotes: 0

ankur
ankur

Reputation: 81

select DATEDIFF(MINUTE, '0:00:00', '00:02:08')

results in :- 2

select DATEDIFF(SECOND, '0:00:00', '00:02:08')

results in :- 128

Using seconds gives a better answer.

So I think the answer can be

SELECT
    EmployeeId
    , seconds_worked = SUM (DATEDIFF (SECOND, '0:00:00', WrkHrs))
FROM
    tbl_employee
GROUP BY
    EmployeeId;

Upvotes: 4

Alex Petrov
Alex Petrov

Reputation: 31

For MS SQL Server, when your WorkingTime is stored as a time, or a varchar in order to sum it up you should consider that:

1) Time format is not supporting sum, so you need to parse it

2) 23:59:59.9999999 is the maximum value for the time.

So, the code that will work to get you the total number of WorkingHours:WorkingMinutes:WorkingSeconds would be the following:

SELECT 
 CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) / 3600),'00') as varchar(max)) + ':' + 
  CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) % 3600 / 60),'00') as varchar(max)) + ':' + 
  CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) % 3600 % 60),'00') as varchar(max)) as WorkingTimeSum
FROM TableName

Upvotes: 3

A Ghazal
A Ghazal

Reputation: 2813

In order to sum up the working hours for an employee you can calculate the difference between the shift start time and end time in minutes and convert it to readable format as following:

    DECLARE @StartTime      datetime = '08:00'
    DECLARE @EndTime        datetime = '10:47'
    DECLARE @durMinutes     int
    DECLARE @duration       nvarchar(5)

    SET @durMinutes = DATEDIFF(MINUTE, @StartTime, @EndTime)

    SET @duration = 
    (SELECT RIGHT('00' + CAST((@durMinutes / 60) AS VARCHAR(2)),2) + ':' + 
            RIGHT('00' + CAST((@durMinutes % 60) AS VARCHAR(2)), 2))

    SELECT @duration

The result : 02:47 two hours and 47 minutes

Upvotes: 4

Murugan
Murugan

Reputation: 111

DECLARE @Tab TABLE
(
    data CHAR(5)
)

INSERT @Tab
SELECT '25:30' UNION ALL
SELECT '31:45' UNION ALL
SELECT '16:00'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, 
    '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
    SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * 
        LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
    ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * 
        SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes
    FROM @Tab
) AS d

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
FROM dbo.table 
-- WHERE ...
GROUP BY EmployeeID;

You can format it pretty on the front end. Or in T-SQL:

;WITH w(e, mw) AS
(
    SELECT EmployeeID, SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
    FROM dbo.table 
    -- WHERE ...
    GROUP BY EmployeeID
)
SELECT EmployeeID = e,
  WrkHrs = RTRIM(mw/60) + ':' + RIGHT('0' + RTRIM(mw%60),2)
  FROM w;

However, you're using the wrong data type. TIME is used to indicate a point in time, not an interval or duration. Wouldn't it make sense to store their work hours in two distinct columns, StartTime and EndTime?

Upvotes: 44

Related Questions