modelrail.otenko Trains, computers, vintage, retro, model railways… the lot…

SqlDateTime vs DateTime (The battle of the Milliseconds)

This was a good one. I had stored a data row both locally in SQL CE 4.0 and then remotely via a WCF service to SQL Express 10.50.4000. I also then stored the 'most recent' date of the most recent local row in a local 'settings' table as an nvarchar. This was formatted as yyyy-MM-dd HH:ii:ss.fff. I'd then pull this date from the settings table and send it to the remote service that asked are there any records newer than this date?.

The remote service would usually repond with the correct answer, when there were newer rows. But, every so often, it would also respond with Yes! In fact there is! Here is the record with the date you just specified!. WTH? I sent the remote service the exact date and it responds with the same record, which is supposedly newer (no greater-than-or-equals here) when it is exactly the same? ... or is it?

It should not have returned this record. The date should have matched the check date I sent the service and the is there a record with a date greater than the check date should not have been true...

DateTime objects stored in MSSQL lose accuracy

Turns out that, in the land of the datetime data format in MSSQL, that the accuracy is not maintained.

MSSQL, with the datetime format, can only store milliseconds of ##0, ##3, ##7. There's a lot of posts on this splattered all over the web.

Be careful when storing a datetime as a string

So what was my issue? I was getting the datetime from my local record and storing this as a string in another table, called settings, which was a key-value-paired one-size-fits-all user-settings table.

I was formatting it to the correct SQL format, preserving the milliseconds. Of course, I could, in this case, store any 3-digit millisecond value I required.

When I then read that back as a datetime in C#, the value was rehydrated correctly.

When I then sent that to the server, the value was up to 2 milliseconds less/greater than the ACTUAL record in my local db and the remote db. Therefore the server would, every so often, return the record I had based my date off... because I was storing it as text more accurately than MSSQL was in the datetime format.

SqlDateTime to the rescue

C# has a data type known as SqlDateTime. If you grab your DateTime from any object, pipe it into the constructor of a new SqlDateTime and then grab the .Value on the other end, you'll wind up with the DateTime as it would be stored in the database!

Tada! You'll no longer have to worry about inaccurate milliseconds!

In fact, you should probably switch ALL DateTimes in your C# app to SqlDateTimes if you want to get rid of shitty little bugs that will only occur when you do something at an exact millisecond that CANNOT BE STORED IN MSSQL.

End Rant.