Sql-server – Operand data type time is invalid for add or subtract operators

operatorsql servertime

I'll to calculate how many hours a person has worked on SQL server. For that I'm storing the Arrival, Departure and Break in my database table as time(7). A bit for DepartureNextDay determinate that the Departure is on or after midnight or not.

Here is an example of my data:

| ID | Arrival  | Depatrure | Break    | DepartureNextDay |
| -- | -------- | --------- | -------- | ---------------- |
| 1  | 23:00:00 | 01:00:00  | 00:00:00 | 1                |
| 2  | 08:00:00 | 17:00:00  | 01:00:00 | 0                |

Now I'll calculate the worked hours using this formula:

Departure - Arrival - Break

When DepartureNextDay is 1 there must 24 hours being added to Departure to get a positive result. So the results must be this:

| ID | WorkedHours | 
| -- | ----------- |
| 1  | 02:00:00    |
| 2  | 08:00:00    |

For this I'm using this code:

select (Departure + (case DepartureNextDay 
                         when 1 then convert(time(7), '24:00:00') --> this is invalid too because it's bigger then 23:59:59
                         else convert(time(7), '00:00:00')
                      end
                    )) - Arrival - Break as WorkedHours 
from WorkTable

But I've this error:

Operand data type time is invalid for add/subtract (when case is removed) operator.

How could I do this?

P.S.: I'm storing the DepartureNextDay on a different column because:

  1. time(7) must between 00:00:00 and 23:59:59 (most important reason)
  2. It's more logical to store 1 o'clock A.M. as 01:00:00 and not as 1.01:00:00 or 25:00:00 and keep DepartureNextDay in a different column.

Best Answer

You should use datetime instead of time, nevertheless it is also possible using your current data:

;with temp
AS
(
    SELECT t.ID,
    CAST(t.Arrival AS time(7)) Arrival,
    CAST(t.Departure AS time(7)) Departure,
    CAST(t.[Break] AS time(7)) [Break],
    DepartureNextDate
    FROM (
        VALUES
        (1, '23:00:00', '01:00:00', '00:00:00', 1),
        (2, '08:00:00', '17:00:00', '01:00:00', 0)
    )t(ID, Arrival, Departure, [Break], DepartureNextDate)

)
SELECT ID, CONVERT(time(7),
    CAST(Departure AS Datetime) 
    + DATEADD(DAY, DepartureNextDate, '') 
    - CAST(Arrival AS Datetime)
    - CAST([Break] AS Datetime)
    )
FROM temp
Related Question