How To Get Fractions of a Second from ADO adDBTimeStamp Field

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.

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.

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.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: For information on the ADO adDBTimeStamp datatype, please see the ADO 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"