HOWTO: Get Fractions of a Second from ADO adDBTimeStamp Field

• ActiveX Data Objects (ADO), versions 1.5, 2.0
• 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 1.5 or 2.0 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
   
          'We'll use the OLE DB for SQL Provider, Local, Trusted login
          cn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=Pubs;"
          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 SQL Server DATEPART function, please see the following article in the Microsoft Knowledge Base:

Q186265 HOWTO: Use the SQL Server DATEPART Function to Get Milliseconds

Additional query words: kbADO150 kbADO200 kbSQLServ kbvb

Keywords          : kbADO150 kbADO200 kbDatabase kbSQLServ kbVBp500 kbVBp600 
Version           : WINDOWS:1.5,2.0,5.0,6.0
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: August 9, 1999