CODEWITHSUNDEEP
sqlsql-server
Mohit
Mohit

Reputation: 2217

How to Sum (Time(n)) in Sql Server?

How want to use the sum of the time(n) operator so that i can calculate the overall total of the time but Sql server saying can't add the Time(n) column

i have a casted column which contain difference of two dates, and being casted as Time(n) by me. Now i want to add those column to get how much time i had used in total How much hours minute and seconds so i apply

select Sum(cast ((date1-date2) as Time(0))) from ABC_tbl

where date1 is reaching time and date2 is startingtime in Date format and i want to total of all hours

Upvotes: 1

Views: 5725

Answers (4)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Try this:

DECLARE 
    @MidnightTime TIME = '00:00:00.0000000',
    @MidnightDateTime DATETIME2 = '0001-01-01 00:00:00.0000000';

SELECT  SumOfTime = DATEADD(SECOND, SUM ( DATEDIFF(SECOND, @MidnightTime, x.Col1) ), @MidnightDateTime)
FROM (VALUES 
    (1, CONVERT(TIME, '10:10:10.0000001')),
    (2, CONVERT(TIME, '00:00:05.0000002')),
    (3, CONVERT(TIME, '23:59:59.0000003'))
) x(ID, Col1)
/*
SumOfTime
---------------------------
0001-01-02 10:10:14.0000000 = 1 day (!), 10 hours, 10 minutes, 14 seconds
*/

Note: instead of SECOND you could use another precision: MINUTE, HOUR or ... NANOSECOND (see section Arguments > datepart). Using a higher precision could leads to Arithmetic overflow errors (use CONVERT(BIGINT|NUMERIC(...,0), ...).

Note #2: because the precision is SECOND the result (SumOfTime) has 0000000 nanoseconds.

Upvotes: 0

Muhab
Muhab

Reputation: 312

select Sum(DATEDIFF(Minute,date1,date2)) AS TIME from ABC_tbl

u have to calculate the date difference with DATEDIFF function then use SUM function to calculate your sum of time. you can change Minute to Second-Hour-month etc..

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6073

Hope this example help you.

DECLARE @A TABLE (SD TIME(0),ED TIME(0)) 

INSERT INTO @A VALUES 
('09:01:09','17:59:09'),
('09:08:09','16:10:09'),
('08:55:05','18:00:00')

SELECT  SUM(DATEDIFF(MINUTE,SD,ED)) SUM_IN_MINUTES,
        SUM(DATEDIFF(HOUR,SD,ED)) SUM_IN_HOURS
FROM    @A

Result:

SUM_IN_MINUTES  | SUM_IN_HOURS
---------------------------------------
1505            |       25

Upvotes: 1

Dan
Dan

Reputation: 10680

Convert the time to an integer value before you sum it (for example, seconds):

SELECT SUM(
        datediff(second, '00:00:00', [TimeCol])
    )
FROM
    ...

Replace [TimeCol] with the name of the Time(n) column. This gives you the total time in seconds, which you can then easily convert to minutes, hours, etc...

Upvotes: 4

Related Questions