By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST(ID int, CheckIn time, CheckOut time);
INSERT INTO TEST VALUES(1, '03:00:00', '10:00:00'),(2, '10:26:13', '03:12:15'),(3, '18:00:00', '03:00:00');
-- Below SQL-Server 2012
SELECT CASE WHEN CheckIn > CheckOut
THEN CONVERT(VARCHAR(20), 86400 - DATEADD(SECOND, DATEDIFF(SECOND, CheckOut, CheckIn), '00:00:00'), 108)
ELSE CONVERT(VARCHAR(20),DATEADD(SECOND, DATEDIFF(SECOND, CheckIn, CheckOut), '00:00:00'), 108)
END Elapsed_Time
FROM TEST
-- SQL-Server 2012 or above
SELECT CASE WHEN CheckIn > CheckOut
THEN FORMAT(86400 - DATEADD(SECOND, DATEDIFF(SECOND, CheckOut, CheckIn), '00:00:00'), 'hh\:mm\:ss')
ELSE FORMAT(DATEADD(SECOND, DATEDIFF(SECOND, CheckIn, CheckOut), '00:00:00'), 'hh\:mm\:ss')
END Elapsed_Time
FROM TEST
| Elapsed_Time |
|---|
| 07:00:00 |
| 16:46:02 |
| 09:00:00 |
| Elapsed_Time |
|---|
| 07:00:00 |
| 04:46:02 |
| 09:00:00 |