Time In Minutes (int or ceiling)

[myan]

I have 3 DateTime Fields Reported, onsite and offsite

T_reported =15/06/2005 04:00:00 PM
T_OnSite = 15/06/2005 06:00:00 PM
T_OffSite = 15/06/2005 07:00:00 PM

INT((T_ONSITE-T_REPORTED)/60) = 120 Minutes
INT((T_OFFSITE -T_ONSITE)/60) = 59 Minutes

I'm thinking rounding errors and tried Ceiling, but that gives me 121 and 60!

Any thoughts?

I scoured the Threads and most people are using the INT to convert into minutes for datediff functions are they getting this rounding problem and have never noticed?

Regards
Paul

 

[Mike Gagnon]

This is a long-know issue

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q78113

Here is another proof of the problem

SET DECIMAL TO 2
T_reported =CTOT('15/06/2005 04:00:00 PM')
T_OnSite = CTOT('15/06/2005 06:00:00 PM')
T_OffSite = CTOT('15/06/2005 07:00:00 PM')
var1=(T_OFFSITE -T_ONSITE)/60
?var1
?INT(var1)
SET DECIMALS TO 16
?var1

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[bborissov]

This is a bug in floating poin calculations try this:

T_reported = DATETIME(2005,6,15,16,0,0)
T_OnSite   = DATETIME(2005,6,15,18,0,0)
T_OffSite  = DATETIME(2005,6,15,19,0,0)

SET DECIMALS TO 17
m1 = ((T_ONSITE-T_REPORTED)/60)
m2 = (T_OFFSITE -T_ONSITE)/60
? m1                                &&->120.000000223517400000
? CEILING(m1)                       &&->121
? CEILING(INT(m1))                  &&->120
? m2                                &&->59.999999776482580000
? CEILING((T_OFFSITE -T_ONSITE)/60) &&->60

Borislav Borissov

 

[Mike Lewis]

If this is indeed a floating-point issue, than surely neither INT() or CEILING() is correct? Given that the error could occur on either side of the integral number of seconds, shouldn't the correct function to use be ROUND()?

Borislav,

I disagree that this is a bug. This type of round-off error is inherent in the way that floating-point numbers work. It is built into the IEEE 754 spec, and is the price you pay for being able to store very high and very low numbers in a relatively small number of bytes.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[bborissov]

OK, it is not bug, but you always must keep in mind that it can happen :-(. It is very annoing that you can't compare the results of some operations directly

Borislav Borissov

 

[mmerlinn]

More info about rounding problems here:

thread184-531793

`