SQL Calculate hours over night with static date

Question

I'm writing Queries on a system someone else installed, so tables can not be changed here.

problem: I have a table where i've got Date, timeIN and timeOUT take the following records;

     date               |       timeIN      | timerOUT
-------------------------------------------------
2016-01-01 00:00:00.00  | 2000-01-01 07:00  | 2000-01-01 15:00       DATEDIFF = 8H
2016-01-02 00:00:00.00  | 2000-01-01 07:00  | 2000-01-01 15:00       DATEDIFF = 8H
2016-01-05 00:00:00.00  | 2000-01-01 23:00  | 2000-01-01 07:00       DATEDIFF = -16H

How can i get DATEDIFF = 8H from record number 3? The problem here is that all timeIN and timeOUT stamps have the same dummy date.

Answer 1

You can use CASE expression inside the DATEDIFF function:

SELECT
    Diff = 
        DATEDIFF(
            HOUR, 
            timeIn, 
            CASE 
                WHEN timeOut < timeIn THEN DATEADD(DAY, 1, timeOut) 
                ELSE timeOut 
            END
        )
FROM tbl

This will add one day on timeOut if it's less than the timeIn.