HOWTO: Get Fractions of a Second from ADO adDBTimeStamp Field

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
• Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0

SUMMARY

The ActiveX Data Objects (ADO) field type 135, adDBTimeStamp, represents a date-time stamp in the form of "yyyymmddhhmmss" plus a fraction in billionths. Therefore, an ADO adDBTimeStamp field can contain the fraction returned from a server Date/Time field. For example, an ADO adDBTimeStamp field can contain the fractional portion of a SQL Server 6.5 DATETIME field.

If you try to assign the ADO adDBTimeStamp field to a Visual Basic DATE datatype variable or to output the field value, Visual Basic drops the fractional portion of seconds for the adDBTimeStamp field. This article demonstrates how to retrieve the fractional portion of seconds from an adDBTimeStamp field.

MORE INFORMATION

You may use a function, such as GetMilliseconds, to retrieve the fractional portion of an adDBTimeStamp field and convert the billionths to fractions of a second. The following code uses the Visual Basic decimal datatype to help perform the conversion, but you could create a string function to parse the fractional value.

One-fourth of the time, ADO returns a time 1 millisecond later than the time returned from the following SQL Server functions:

   DATEPART(MS, fieldname)
   CONVERT(CHAR(24), fieldname, 109) 

Step-by-Step Example

1. In Visual Basic, create a new Standard EXE project.



2. Add a reference to the Microsoft ActiveX Data Objects library.



3. Place a list box on Form1.



4. Set the following properties on the form:




      Form1.Width   8000
      List1.Width   6500 

5. Paste the following code into the Code window of Form1:




   Function GetMilliseconds(ByVal varDateTime As Variant) As Long

     '  The Decimal datatype can store decimal values exactly.
     '  Variables cannot be directly declared as Decimal, so
     '     create a Variant then use CDec( ) to convert to Decimal.
       Dim decConversionFactor As Variant
       Dim decTime As Variant

     'K is used to convert a VB time unit back to seconds
     'K = 86400000 milliseconds per day
       decConversionFactor = CDec(86400000)

     'Store the DateTime value in an exact decimal value called decTime
       decTime = CDec(varDateTime)

     'Make sure the date/time value is positive
       decTime = Abs(decTime)

     'Get rid of the date (whole number), leaving time (decimal)
       decTime = decTime - Int(decTime)

     'Convert to time to seconds
       decTime = (decTime * decConversionFactor)

     'Return the milliseconds
       GetMilliseconds = decTime Mod 1000

   End Function

   Private Sub Form_Click()

       Dim cn As New ADODB.Connection
       Dim rs As New ADODB.Recordset

       Dim strSql As String
       Dim Millisecs As Integer
       Dim Hundredths As Integer

       'Use the OLE DB for SQL Provider, Local, Trusted login
        cn.ConnectionString = "Provider=SQLOLEDB;" & _
           "Initial Catalog=Pubs;Data Source=(local);" & _
            "Integrated Security=SSPI;"
        cn.Open

       'Update table to current date and time
       cn.Execute "UPDATE Titles SET Pubdate = GetDate()"

       'We'll get the date, plus the SQL Server DATEPART value
       strSql = "SELECT Pubdate, DATEPART(MS,Pubdate)AS SQLsDP FROM Titles"
       rs.Open strSql, cn

       Millisecs = GetMilliseconds(rs("Pubdate"))
       'Round.
       Hundredths = (Millisecs + 5) \ 10

       'Display Pubdate, Hundredths, Milliseconds, DATEPART value
       List1.AddItem rs("Pubdate") & vbTab & Hundredths & _
                     vbTab & Millisecs & vbTab & rs("SQLsDP")
       'Clean up
       rs.Close
       cn.Close
       Set rs = Nothing
       Set cn = Nothing

   End Sub

   Private Sub Form_Load()

       'Display Header in Listbox
       List1.AddItem "Pubdate" & vbTab & vbTab & vbTab & "1/100's" & _
                      vbTab & "Millisecs" & vbTab & "DATEPART"
   End Sub
 

6. Run the test project. Click the Form to test.

REFERENCES

For information on the ADO adDBTimeStamp datatype, please see the ADO 1.5, 2.0, or 2.1 Online Documentation for the Type property.

Visual Basic Help; search on: "Date Data Type"

SQL Server Books Online; search on: "datetime", topic: "Date and Time data"

Additional query words:

Keywords : kbADO150 kbADO200 kbDatabase kbSQLServ kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto