Microsoft KB Archive/193869

= How To Get Fractions of a Second from ADO adDBTimeStamp Field =

Article ID: 193869

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q193869



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.

Step-by-Step Example
 In Visual Basic, create a new Standard EXE project. Add a reference to the Microsoft ActiveX Data Objects library. Place a list box on Form1.  Set the following properties on the form:      Form1.Width   8000 List1.Width  6500   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

 Run the test project. Click the Form to test.</li></ol>

<div class="references_section">