Microsoft KB Archive/245374

= PRB: adDBTime and adDBDate Columns Print Incorrect Values with ADO =

Article ID: 245374

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



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.



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.

