PRB: Date Comparison Can fail Using Arithmetic Operators

• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0
• Microsoft Visual Basic for Applications version 5.0
• Microsoft Visual Basic Standard, Professional, and Enterprise Editions for Windows, version 4.0

SYMPTOMS

Comparing two apparently identical dates using the standard comparison operators produces anomalous results.

CAUSE

The Visual Basic Date data type is stored internally as a Double (64 bit floating point). Performing operations on Date variables is subject to the same rounding problems as any other floating point value. This is true even if most of the intrinsic functions, such as DateAdd, are used.

RESOLUTION

1. Use the DateDiff intrinsic function to compare date and time values. DateDiff rounds the input values to the specified tolerance, eliminating floating point errors.



2. Take the difference between two variables of type Date and compare the absolute value of the result to a tolerance value such as 10E-11. If the difference is less than the tolerance, the values may be considered identical.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.



2. Add the following code to the General Declarations section of Form1:

   
         Option Explicit
   
         Private Sub Form_Load()
            Dim Date1 As Date
            Dim Date2 As Date
            Dim Date3 As Date
            Dim Date4 As Date
            Me.Width = 6500
            Me.Height = 3000
            Me.Show
            Date1 = #10/21/1998 8:00:00 AM#
            Date2 = #10/21/1998 8:20:00 AM#
            Date3 = DateAdd("n", 20#, Date1)
            Date4 = Date1 + TimeSerial(0, 20, 0)
            Print "The results are visually identical..."
            Print
            Print "Date2 = "; Date2
            Print "Date3 = "; Date3
            Print "Date4 = "; Date4
            Print
            Print "but the actual values are not"
            Print
            Print Tab(20), "=", "DateDiff", "Actual Difference"
            Print "Date2 = Date3?", Date2 = Date3,
            Print DateDiff("s", Date2, Date3), Date2 - Date3
            Print "Date2 = Date4?", Date2 = Date4,
            Print DateDiff("s", Date2, Date4), Date2 - Date4
            Print "Date3 = Date4?", Date3 = Date4,
            Print DateDiff("s", Date3, Date4), Date3 - Date4
         End Sub
    




3. Run the application and examine the results on the form.

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

Q130514 : ACC: Storing, Calculating, and Comparing Date/Time Data

Q42980 : (Complete) Tutorial to Understand IEEE Floating-Point Errors

Keywords : kbDateTime KbVBA kbVBp400 kbVBp500 kbVBp600 kbGrpVB kbCodeSam
Version : WINDOWS:4.0,5.0,6.0
Platform : WINDOWS
Issue type : kbprb