How to Fix the Problem When ADO Truncates Milliseconds from SQL Server Datetime Columns

By : Brian Lockwood Jul 20, 2002

Printer friendly

If you create a SQL Server table that contains a datetime column and populate it with a GetDate() default then try to query it with ADO, you will discover that the data returned from datetime columns is returned without milliseconds. If you need milliseconds returned, this can be very annoying to deal with.

For example, in VB:

strSQL = "SELECT DateTime FROM DateTest"

objRs.Open strSQL

? objRs(o)

8/9/2002 11:15:27 PM

But if you perform the query within Query Analyzer, you get the true result:

2002-08-09 23:15:27.490

SOLUTION 1 - Do it in Transact-SQL

If you know the query you are going to execute in VB, you can manipulate the SQL string to add the necessary formatting. There are three groups of date formats in SQL Server you can use to to preserve milliseconds

• 9 or 109 for Default + milliseconds
• 13 or 113 for Europian format
• 21 or 121 for ODBC format (that also matches ISO format)

For example,

SELECT CONVERT(varchar, DateTime, 21) FROM DateTest

Returns:

2002-08-09 23:15:27.490

But this solution might not be possible for all situations. Another alternative is to do it in Visual Basic itself.

SOLUTION 2 - Use FormatDateTime Function?

VB provides the FormatDateTime Function, which accepts the VBLongTime constant:

vbLongTime = Displays a time using the time format specified in your computer's regional settings.

For example:

? FormatDateTime(now(), vbLongTime)

11:41:54 AM

Returns:

But again, this does not return milliseconds. I have attempted to configure my regional settings (via the Control Panel) to allow for milliseconds, but: 1) I ran into the same problem as above, and 2) this would create a dependancy on your user's desktop settings for your program to work -- which is not good.