PRB: SQL Server datetime Values Are Randomly Rounded Up in ADO Application (327080)

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 "2002-04-26 08:45:01.500," the value may appear as "4/26/2002 8:45:02 AM" in your application.

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; ":"; 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.

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 = "Provider=SQLOLEDB;" & _
   "Initial Catalog=pubs;Data Source=yourSQL;" & _
   "User Id=yourUser;Password=yourPwd"
cn.Open
        
rs.CursorLocation = adUseServer
rs.CursorType = adOpenDynamic
strSql = "SELECT DateLastModified FROM tblTime"
rs.Open strSql, cn, adOpenDynamic

Do While (Not rs.EOF)
     List1.AddItem rs("DateLastModified")
     rs.MoveNext
Loop
   
rs.Close
cn.Close