How to calculate time difference in T-SQL

Question

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

Answer 1

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