Timestamp accuracy - gained milliseconds

Question

If I store a System.DateTime as a timestamp in SQL using this fluent nhibernate mapping:

Map(x => x.LoggedTimeStamp).CustomType("Timestamp")
                           .Not.Nullable();

When I store and recall the timestamp:

new DateTime(2013, 5, 10, 14, 59, 13, 235);

It comes back equal to new DateTime(2013, 5, 10, 14, 59, 13, 237);

It has gained 2 milliseconds. This is also evident in the SQL Server management studio.

My question is, can I tell NHibernate to use a more accurate field type?

Answer 1

That's a limitation of SQL-Server's standard datetime type.

Rounded to increments of .000, .003, or .007 seconds

Only in SQL Server 2012 2008 and above are there more accurate datetime types. I don't know if you can make NHibernate use those (it would certainly destroy portability).

Alternatively, if this precision is really so important, you might save the datetime values as numbers or even strings, doing the conversion in NHibernate type convertors. I did something like it once (someone was using MySQL's 0000-00-00 date), but it was a long time ago and I don't know if the approach is still valid. Read NHibernate's documentation about custom data types.