DateTime rounding of milliseconds in SQL incorrect

Question

DateTime rounding of milliseconds in SQL incorrect

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

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

Lowell SSC Guru Points: 323740 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