0

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.
  • 1
    IMHO you should use datetime columns and avoid formulas. – McNets Jan 9 at 8:27
  • 2
    Have you considered the case of someone working with DepartureNextDay on the day that daylights saving changes? – Martin Smith Jan 9 at 9:28
  • @MartinSmith: All the times must be entered using the same timezone – H. Pauwelyn Jan 9 at 10:42
0

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
0

A thing you could do without changing the datatypes is to convert form hh:mm:ss to the decimal representations. For that you can

  1. Create a function for calculate time to float:

    create function CalculateTimeAsFloat(
        @input time(7)
    ) returns float
    as begin
        return datepart(hour, @input) + 
               datepart(minute, @input) * (1.0 / 60.0) + 
               datepart(second, @input) * (1.0 / 3600.0)
    end
    
  2. Create a function for calculate float to time:

    create function CalculateFloatAsTime(
        @input float
    ) returns time(7)
    as begin
        declare @hours float = floor(@input);
        declare @minutes float = (@input - @hours) * 60.0;
        declare @seconds float = (@minutes - floor(@minutes)) * 60.0;
    
        declare @text nvarchar(max) = convert(nvarchar, @hours) + ':' + 
                                      convert(nvarchar, floor(@minutes)) + ':' +
                                      convert(nvarchar, @seconds)
    
        return convert(time(7), @text);
    end
    
  3. And then do your formula:

    select dbo.CalculateFloatAsTime(
            dbo.CalculateTimeAsFloat(Departure) + (case DepartureNextDay when 1 then 24 else 0 end) - 
            dbo.CalculateTimeAsFloat(Arrival) - 
            dbo.CalculateTimeAsFloat(Break)
        ) as WorkedHours
    from WorkTable
    

Edit: Found that my function in step 2 is not 100% working. I've updated it to this code:

alter function CalculateFloatAsTime(
    @input float
) returns time(7)
as begin
    declare @hours float = floor(@input);
    declare @minutes float = (@input - @hours) * 60.0;
    declare @seconds float = round((@minutes - floor(@minutes)) * 60.0, 0);

    declare @text nvarchar(max) = convert(nvarchar, @hours) + ':' + 
                                  convert(nvarchar, floor(@minutes) + (case @seconds when 60 then 1 else 0 end)) + ':' +
                                  convert(nvarchar, @seconds - (case @seconds when 60 then 60 else 0 end));

    return convert(time(7), @text);
end

An exceptions will be thrown when @input is 8.16666. It's a rounding error from step 1 and must be 8.16667. Because of that I've added the cases.

Note: This is not 100% correct when using seconds and milliseconds. There are rounding errors happening!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.