3

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:

  • Time From 13:50
  • Time To 14:10

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
Improve this question

2 Answers 2

Reset to default
7

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

Improve this answer
1

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
Improve this answer