PRB: adDBTime and adDBDate Columns Print Incorrect Values with ADO

View products that this article applies to.

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.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

More information

Steps to Reproduce Behavior

Start a new Visual Basic Standard EXE project. Form1 is added by default.
From the Project menu, click References, and select the Microsoft ActiveX Data Objects Library.

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

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.

↑ Back to the top