Dataset not rounding DateTime correctly

overcast · January 30th, 2009, 05:54 PM

My application takes an XML file and loads it into a dataset to do some manipulations before storing it in a SQL Server. Each XML entry has a datetime child node that goes down to the nanosecond. The dataset will round that to the millisecond, example:
2009-01-26 12:00:27.131159540-0800 changes to
2009-01-26 12:00:27.130

This is fine. However, there is a rare case that occurs as such:
2009-01-26 12:00:27.198485020-0800 changes to
2009-01-26 12:00:27.1100

So, in this specific instance, the example of the rare case would normally be organized after the first example shown, but now that the dataset rounded as such it will be the opposite order. I need to have the original order kept.

Is this a bug with the Dataset or is there something I'm missing.

Here is how the code looks when it is loaded:

Code:

protectedvirtualvoid Load()
{
_dataset.EnforceConstraints = false;
_dataset.ReadXmlSchema(_XSDLocation);
_dataset.ReadXml(_name, XmlReadMode.IgnoreSchema);
}

Old Pedant · January 31st, 2009, 02:02 AM

No, SQL Server does *NOT* round "to the millisecond".

http://msdn.microsoft.com/en-us/libr...7(SQL.80).aspx

It rounds to the nearest integer multiple of 3.33 milliseconds.
As that page says, "Values are rounded to increments of .000, .003, or .007 seconds..." So you will never see a value such as xxx.814 -- it would have to be either xxx.813 or xxx.817

Having said all that...

I sure don't see why x.198 would get rounded to x.110

Sounds like a bug in SQL Server and/or the driver.

But in any case, it doesn't sound to me like this is ever going to be accurate enough for your purposes. So I hate to say it, but I think you are going to have to create your own pseudo-datatype to hold you dates and times as accurately as you need them.

I would suggest that you might want to just keep them as strings (VARCHAR/NVARCHAR) if they indeed have the format you show:
2009-01-26 12:00:27.198485020-0800
presumably if you just lopped off the "-0800" you would have values that would always sort correctly, to whatever the recorded accuracy you have. [Even the "-0800" wouldn't interfere unless the number of digits after the decimal point is variable.]

Hmmmm???