CODEWITHSUNDEEP
sql-servert-sql
user2049855
user2049855

Reputation:

How to calculate time difference in T-SQL

I have created a table with columns of datatype time(7)

I want to calculate the time difference between them.

Table time:

 id   timefrom     timeto      result
 --------------------------------------
 1    13:50:00     14:10:00    00:20:00   
 2    11:10:00     11:00:00    23:50:00

For example:

Result should show 00:20.

Is there a function for this?

DATEDIFF(hour, UseTimeFrom, UseTimeTo) hourtime,
(DATEDIFF(MINUTE, UseTimeFrom , UseTimeTo)) - (((DATEDIFF(hour, UseTimeFrom, UseTimeTo)) * 60)) as mintime

Upvotes: 3

Views: 10137

Answers (2)

marc_s
marc_s

Reputation: 754230

There's no built-in function - but you could relatively easily write your own T-SQL stored function to calculate this - something like this:

CREATE FUNCTION dbo.TimeDifference (@FromTime TIME(7), @ToTime TIME(7))
RETURNS VARCHAR(10)
AS BEGIN
    DECLARE @Diff INT = DATEDIFF(SECOND, @FromTime, @ToTime)

    DECLARE @DiffHours INT = @Diff / 3600;
    DECLARE @DiffMinutes INT = (@Diff % 3600) / 60;
    DECLARE @DiffSeconds INT = ((@Diff % 3600) % 60);

    DECLARE @ResultString VARCHAR(10)

    SET @ResultString = RIGHT('00' + CAST(@DiffHours AS VARCHAR(2)), 2) + ':' +
                        RIGHT('00' + CAST(@DiffMinutes AS VARCHAR(2)), 2) + ':' +
                        RIGHT('00' + CAST(@DiffSeconds AS VARCHAR(2)), 2)

    RETURN @ResultString
END

This function uses the integer division (/) and integer remainder (%) operators to calculate the number of hours, minutes and seconds that those two times are apart, and then concatenates those together into a string as you are looking for.

SELECT 
    dbo.TimeDifference('13:50:00', '14:10:00'),
    dbo.TimeDifference('13:50:00', '15:51:05'),
    dbo.TimeDifference('13:50:00', '15:35:45')

Sample output:

00:20:00     02:01:05     01:45:45

Upvotes: 2

James Z
James Z

Reputation: 12318

You can do it this way:

select *, convert(time, convert(datetime, timeto) - convert(datetime, timefrom)) 
from table1

This will convert the times to datetime for day 0 (1.1.1900) and then do the calculation and in case the timeto is smaller it will get to previous day, but convert to time will get the time part from it.

Example in SQL Fiddle

Upvotes: 7

Related Questions