How To Get Fractions of a Second from ADO adDBTimeStamp Field (193869)

MORE INFORMATION

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.