SQL Server 2000 and Milliseconds (datetime data type)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vikram.mankar@gmail.com - 18 Jan 2007 19:15 GMT

I'm running into a constant issue of SQL Server modifying the
millisecond part of a timestamp insert from another application. The
application inserts timestamp which includes a millisecond portion as a
string (varchar). But when an SQL Server moves this data to another
table (for reporting), the string is inserted in a datetime field, the
millisecond field invariably changes by 1-2 milliseconds for every
single data point inserted. Given the time critical nature of this data
(to a millisecond), its almost impossible to avoid this other than to
leave the data as string type. But this drives the analytical reporting
folks wild as report queries based on time criteria are getting messed
up. Any ideas how to force SQL Server not to mess around with the
millisecond value? Does this problem exist with SQL Server 2005 as well?

Reply to this Message

Roy Harvey - 18 Jan 2007 20:58 GMT

This is a documented behavior of the datetime datatype. As the Books
On Line says: "Values are rounded to increments of .000, .003, or .007
seconds". It applies to all versions, and I would not expect it to
ever change.

If you must keep it to the exact millisecond then you can not use
datetime.

You could split the information into two columns, say one part for the
date (could use smalldatetime) and the other for milliseconds since
midnight. Or, since smalldatetime is to the minute the second column
would just have seconds and milliseconds. There are countless
variations possible, none will make processing simple.

Roy Harvey
Beacon Falls, CT

>I'm running into a constant issue of SQL Server modifying the
>millisecond part of a timestamp insert from another application. The
[quoted text clipped - 8 lines]
>up. Any ideas how to force SQL Server not to mess around with the
>millisecond value? Does this problem exist with SQL Server 2005 as well?

Reply to this Message

vikram.mankar@gmail.com - 30 Jan 2007 15:18 GMT

I should have known that! Darn...

Thanks.

> This is a documented behavior of the datetime datatype. As the Books
> On Line says: "Values are rounded to increments of .000, .003, or .007
[quoted text clipped - 25 lines]
> >up. Any ideas how to forceSQLServernot to mess around with the
> >millisecondvalue? Does this problem exist withSQLServer2005 as well?- Hide quoted text -- Show quoted text -

Reply to this Message