sql_types.keep_datetime Reduces Precision of DateTimeOffset

[dennismsmith]

In NHibernate 5.x, when using the sql_types.keep_datetime option for SQL Server in order to use the DATETIME rather than DATETIME2 type, it has the side effect of reducing the precision of any DATETIMEOFFSET type. In particular, you only get two digits of sub-second precision. In NHibernate 4.x, the DATETIMEOFFSET type got the full seven digits of precision.

The documentation for sql_types.keep_datetime doesn't mention DATETIMEOFFSET at all, so I would not have expected it to modify the behaviour of DATETIMEOFFSET:

Since NHibernate v5.0 and if the dialect supports it, DbType.DateTime2 is used instead of DbType.DateTime. This may be disabled by setting sql_types.keep_datetime to true. Defaults to false.

My database has a combination of DATETIME and DATETIMEOFFSET columns in it, which is why I need to use sql_types.keep_datetime.

This can be tested with the following:

configuration.SetProperty(Environment.SqlTypesKeepDateTime, "true");
using (ISession s = OpenSession())
using (ITransaction t = s.BeginTransaction())
{
	DateTimeOffsetType type = new DateTimeOffsetType();
	DateTimeOffset value = (DateTimeOffset) type.Seed(s.GetSessionImplementation());

	string stringRepresentation = value.ToString("o");
	string fractionalSeconds = stringRepresentation.Substring(stringRepresentation.IndexOf('.') + 1, 7);
	Assert.That(fractionalSeconds, Does.Not.EndWith("00000"));
}

If you don't have configuration.SetProperty(Environment.SqlTypesKeepDateTime, "true"); set, it will consistently pass.

[fredericDelaporte]

This is a change done by #703. We have cleaned-up the date and time types handling for better consistency, fixing quite a bunch of bugs. As part of this process, the seeding has been rounded according to the dialect declared datetime resolution. This is required to avoid bugs with "timestamp" version fields, where the seeded value would not be yielded back the same after a round-trip to the database.
The "keep datetime" option causes the SQL Server dialect to revert back to the datetime resolution, 10ms, as stated in releases notes. (In fact datetime resolution is 3.333ms, which causes comparison bugs with ODBC if not rounded, thus rounding to 10ms.)

SQL Server 2008+ timestamp resolution is now 100ns in accordance with datetime2 capabilities, down from 10ms previously. This can be reverted with sql_types.keep_datetime setting.

So the seeding of datetimeoffset is affected too. The fact that datetimeoffset has a better precision with SQL-Server than datetime is vendor dependent (specific to SQL-Server), so it is unlikely we will try to account for it, especially since "keep datetime" is a feature meant for legacy schema.

For your specific case, have you checked if you actually need to "keep datetime" for your datetime + datetimeoffset setup? I have not checked again, but from memory, I would say this is important mainly for hbm2ddl, which is to say, schema generation and validation.
If you do not use hbm2ddl, it may not have much impact.
Well, query parameters may be "mistyped" which could have an effect on query performances, so you would need to do some testing to check it does not cause issues.

Otherwise you may also use a custom dialect deriving from the one you currently used, and overriding TimestampResolutionInTicks to yield 1 (1 tick => 100ns) in spite of your configured "keep datetime" option.

[dennismsmith]

This may all be a problem with my interpretation of the documentation/release notes.

I had interpreted the release notes comment

SQL Server 2008+ dialects now use datetime2 instead of datetime for all date-time types, including timestamp. This can be reverted with sql_types.keep_datetime setting.

to mean that if you had any columns in your SQL Server schema that use the DATETIME type that you must set sql_types.keep_datetime to true for even basic CRUD to work. If that's not the case, I can set sql_types.keep_datetime to false and I think my problem would be resolved. In my case, we manage all of the modifications to our schema manually, so we don't use use hbm2ddl.

I had also interpreted the release notes comment

SQL Server 2008+ timestamp resolution is now 100ns in accordance with datetime2 capabilities, down from 10ms previously. This can be reverted with sql_types.keep_datetime setting.

to mean that for all of these types they would retain their NHibernate 4.x behaviour, which is not the case for DATETIMEOFFSET as setting sql_types.keep_datetime will cause it to get a lower precision than in NHibernate 4.x.

[fredericDelaporte]

Worth reading: #2289.
If you have queries relying on equality with datetime values and their 3.333...ms resolution, only one third of them will still work, due to parameters being typed DateTime2, and due to that terrible fractional resolution. With datetime, the milliseconds unit can be 0ms, 3.333...ms or 6.6666...ms. When converted to datetime2, it then become 0ms (no issue) 3ms or 7ms, both later cases failing to compare to a datetime column representing the "same" value. (They will be considered unequal.)

So in such case you have to use the keep datetime parameter, and dodge the resolution trouble by using a custom dialect overriding it as explained in a previous comment. (Or better migrate your datetime columns to datetime2.)

Anyway, closing this as not an issue.