PRB: adDBTime and adDBDate Columns Print Incorrect Values with ADO (245374)


The information in this article applies to:

  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft Visual Basic Learning Edition for Windows 6.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q245374

SYMPTOMS

Passing a time value to adDBTime or adDBDate ADO columns prints incorrect values.

CAUSE

The time and date conversion is happening inside the conversion library. The conversion results always return back as variant time.

From the Platform SDK: Automation documentation, here is how the variant time is defined:

"A variant time is stored as an 8-byte real value (double), representing a date between January 1, 100 and December 31, 9999, inclusive. The value 2.0 represents January 1, 1900; 3.0 represents January 2, 1900, and so on. Adding 1 to the value increments the date by a day. The fractional part of the value represents the time of day. Therefore, 2.5 represents noon on January 1, 1900; 3.25 represents 6:00 A.M. on January 2, 1900, and so on. Negative numbers represent the dates prior to December 30, 1899."

In the case of passing a time value to a adDBTime column, the system date is used to substitute for the Date part (yyyymmdd) and the time part is taken from the user's input.

In the case of passing a time value to a adDBDate column, the time string (that is, "11:22:33") is passed in, it is parsed as the time part of the date, and the date is stored as 0. When this is retrieved back again, the variant time is just going to have 0. The data is not being modified in any way by ADO. It just hands back the data given converting it to the desired format.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start a new Visual Basic Standard EXE project. Form1 is added by default.
  2. From the Project menu, click References, and select the Microsoft ActiveX Data Objects Library.
  3. Place the following code in the general declaration section of Form1:
    Private Sub Form_Load()
    
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection

    rs.Fields.Append "fldTime", adDBTime
    rs.Fields.Append "fldDate", adDBDate
    
    rs.Open
    
    rs.AddNew
    
    rs!fldTime = "11:22:33"
    rs!fldDate = "11:22:33"
    
    rs.Update
    
    If rs.Fields(0).Type = adDBTime Then Debug.Print "adDBTime = " & rs.Fields(0).Value
    If rs.Fields(1).Type = adDBDate Then Debug.Print "adDBDate = " & rs.Fields(1).Value

End Sub
  4. Press the F5 key to run the code. You would get the following results in the immediate window:

    adDBTime = 02/29/2000 11:22:33 AM
    adDBDate = 12:00:00 AM

    NOTE: In the preceding example, 02/29/2000 was the current date.

REFERENCES

For more information, please refer to the Platform SDK for Windows 2000.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbDatabase kbprb KB245374
©2002 Microsoft Corporation. All rights reserved.