DateTime rounding of milliseconds in SQL incorrect

Question

DateTime rounding of milliseconds in SQL incorrect

industry86-998684

SSC Veteran

Points: 274
More actions
January 30, 2009 at 2:33 pm

#94764

My application takes an XML file and loads it into a dataset to do some stuff with it before storing it in SQL Server. Each entry has a DateTime stamp of when the particular entry was created.
When the DateTime object is loaded in it shortens the milliseconds down to the top 3.
So: 2009-01-26 12:00:27.131159540-0800
turns into 2009-01-26 12:00:27.130
This is fine. However, my problem is the rare case of:
2009-01-26 12:00:27.198485020-0800
turns into:
2009-01-26 12:00:27.1100
It appears there is a rounding error in loading the data into the dataset. I tested this off the production server and the same thing happens locally.
Is this a known bug or am I just missing something? I need these entries this accurate because I run pattern matching on the precise order of these entries.
Thanks and let me know if I forgot to include anything important in helping me.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

well, SQL servers datetime datatype is only accurate to 3 milliseconds increments...so if you try to increment by milliseconds it goes from 0,3,6,9 all the way up to 997

to preserve the accuracy of your data, i would save your real data as a varchar, and add a calculated field of type datetime based on the convert of the varchar.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

industry86-998684 SSC Veteran Points: 274 More actions · Answer 2

Thanks for the reply. I figured that was why the rounding was occuring, but that is incorrect. .198 rounded up is not .1100.

I'll take a look at your idea though and see if that is feasible to do.

Thanks again!

noeld SSC Guru Points: 96590 More actions · Answer 3

SQL 2008 supports 100 nanosecond time resolution!

* Noel

Gift Peddie SSC Guru Points: 73570 More actions · Answer 4

Yes that is the same with .NET DateTime but it require Double data type because INT resolution is limited.

So you can either use time in SQL Server or use .NET you could get millisecond very easily because .NET DateTime can get you ticks so you just need your DateTime defined as Double to get milliseconds and add it to SQL Server Nvachar column. The reason you are not getting the correct value could be SQL Server DateTime may be using INT for DateTime Data Type which does not come with the resolution needed to generate millisecond easily.

Kind regards,
Gift Peddie