Subtracting time in SQL query rounding errors

Question

I have a query that calculates the hours worked from dates which includes times.

e.g 1/07/2011 7:00:00 AM - 1/07/2011 5:40:00 PM - 0.5 = 10.166666

The SQL is

    SELECT    entityID, StaffID, ActualDate, 
    DATEDIFF(minute, StartTime, EndTime) / 60.0 lunch AS HoursWorked
    FROM      dbo.qasiteTimesheet

StartTime and EndTime are both datetime type. Lunch is numeric(9,1).

If I add whole hours e.g 6:00 AM - 5:00 PM it works fine, but add 34, 10, 2 it rounds up incorrectly.

What am I doing wrong?

`1.` Your example has nothing to do with your query (where's the `-0.5` coming from?). `2.` I don't know what you mean by add `34, 10, 2`. `3.` Your example (without -0.5) gives me 640 minutes or 10.6666 hours wich I believe is correct. — Lieven Keersmaekers, Jul 04 '11 at 06:46
@Lieven: It's probable that a `-` is missing between `60.0` and `lunch`. — Andriy M, Jul 04 '11 at 07:17
@webb: Maybe you should store minutes instead of hours in `Lunch`. That way the calculations might be more exact. The formula, of course, would be slightly different: `(DATEDIFF(minute, StartTime, EndTime) - lunch) / 60.0 AS HoursWorked`. — Andriy M, Jul 04 '11 at 07:22

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

60.0 is decimal(3,2) and the rules for decimal division are quite complex.

lunch decimal(9,1) too, so you can never have more than 6 minute accuracy here

Put together, it's a simply precision problem.

I'm not going to work it out fully (you can see my answer here) so try this, or an explicit CAST, or some combination

(DATEDIFF(minute, StartTime, EndTime) - (60.0*lunch)) / 60.0 - AS HoursWorked

edited May 23 '17 at 10:33

Community

answered Jul 04 '11 at 07:24

gbn

1 Answers1