Microsoft KB Archive/327080

= PRB: SQL Server datetime Values Are Randomly Rounded Up in ADO Application =

Article ID: 327080

Article Last Modified on 5/28/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q327080



SYMPTOMS
In an ADO application, when you try to retrieve datetime values from Microsoft SQL Server 2000 by using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), the seconds portion of the datetime values are rounded up randomly if the time portion has 500 milliseconds.

For example, if the datetime value in the database is &quot;2002-04-26 08:45:01.500,&quot; the value may appear as &quot;4/26/2002 8:45:02 AM&quot; in your application.



CAUSE
This problem occurs because of how the OLE Automation layer (Oleaut32.dll) formats the datetime values. ADO uses this layer when it formats data.



RESOLUTION
To work around this problem, use one of the following methods:   Use the following Microsoft Visual Basic 6.0 code, which demonstrates one way to retrieve the seconds and the milliseconds of the datetime data: Private Sub DisplaySecsMillsecs(ByVal varDateTime As Variant) Dim decConversionFactor As Variant Dim decTime As Variant Dim milliseconds As Variant Dim seconds As Variant

decConversionFactor = CDec(86400000) 'Store the datetime value in an exact decimal value called decTime decTime = CDec(varDateTime)

'Make sure that the datetime value is positive. decTime = Abs(decTime)

'Remove the date (whole number), and leave the time (decimal). decTime = decTime - Int(decTime)

'Convert the time to seconds. decTime = (decTime * decConversionFactor) seconds = ((Int(decTime) - (decTime Mod 1000)) / 1000) Mod 60 milliseconds = decTime Mod 1000 'Display seconds and milliseconds of the datetime value that is passed. Debug.Print seconds; &quot;:&quot;; milliseconds

End Sub   Use the SQL Server CONVERT function in the SELECT statement to return the datetime value as character data instead of datetime as follows: SELECT convert(varchar(30), datelastmodified, 109) from tblTime For additional date formatting styles, see SQL Server Books Online. 



STATUS
This behavior is by design.



MORE INFORMATION
SQL Enterprise Manager also demonstrates the same behavior.

Steps to Reproduce the Behavior
  Use the following SQL script to create a table and to add several datetime values: if exists (select 1           from  sysobjects           where  id = object_id('dbo.tblTime')            and   type = 'U') drop table dbo.tblTime go create table dbo.tblTime (DateLastModified datetime not null) go declare @t datetime declare @i int set @i=1 set @t='04/26/2002 08:45:00.500 AM' insert tblTime (datelastmodified) values (convert(datetime,@t)) while @i<120 begin set @t=(select top 1 datelastmodified from tbltime) set @t=dateadd(s,@i,@t) insert tblTime (datelastmodified) values (convert(datetime,@t)) set @i=@i+1 end   Use the following code in Visual Basic 6.0 by using ADO:

NOTE: The code uses a ListBox control to display the values. You must add a ListBox control that is named List1 to your form. Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim strSql As String Dim Millisecs As Integer

'Use the OLE DB Provider for SQL Server. cn.ConnectionString = &quot;Provider=SQLOLEDB;&quot; & _ &quot;Initial Catalog=pubs;Data Source=yourSQL;&quot; & _ &quot;User Id=yourUser;Password=yourPwd&quot; cn.Open rs.CursorLocation = adUseServer rs.CursorType = adOpenDynamic strSql = &quot;SELECT DateLastModified FROM tblTime&quot; rs.Open strSql, cn, adOpenDynamic

Do While (Not rs.EOF) List1.AddItem rs(&quot;DateLastModified&quot;) rs.MoveNext Loop rs.Close cn.Close </li></ol>

Additional query words: rounding round round-up date-time date time

Keywords: kbprb KB327080

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.