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?

CodePudding user response：

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

CodePudding user response：

If you want to add/subtract dates/datetimes you need to use the relevant functions to do so as follows:

select *, convert(varchar(5), dateadd(minute, datediff(minute, [TimeOut], TimeIn), convert(time, '00:00'))) TimeDifference
    -- , CONVERT(TIME, [TimeOut], TimeIn) -- gives an error anyway?
from (
    values
    (convert(time, '05:45 PM'), convert(time, '03:30 PM'))
) x (TimeIn, [TimeOut]);

Returns: