Search code examples
sql-serverformattingdifference

Convert a time calculation to time in HH:MM

I am looking to create a calculation in SQL Server that will display the time difference in HH_MM from a time column.

For example, the difference in hours and minutes between

5:45 PM - 3:30 PM = 2:15 (desired output)

The closest I have been able to get is this:

CONVERT(TIME,cals_END_time - cals_START_time) = 02:15:00.0000000

How do I go about 'trimming' it down to 2:15?

Solution

  • Assuming the columns are datetime, use the format function to format the time value:

    select format(cast(cals_end_time - cals_start_time as time), N'h\:mm')
from (values
    (cast('2022-07-07 15:30:00' as datetime), cast('2022-07-07 17:45:00' as datetime))
) as tests(cals_start_time, cals_end_time)

    DB<>Fiddle