NHibernate Date Format Issue for Converted Database

Question

We have an app using NHibernate to connect to an Oracle database. I have been tasked with converting the Oracle DB to a SQL Server DB in such a way that switching between the DBs is as simple as changing the NHibernate drivers. In my Oracle DB I have a column defined as TIMESTAMP(6) WITH TIME ZONE. This column has been converted to a SQL Server datetimeoffset(6) data type. The mapping in my NHibernate hbm.xml file looks like this:

<property name="checkoutDate" type="DateTime">
      <column name="CHECKOUT_DATE" sql-type="TIMESTAMP(6) WITH TIME ZONE" not-null="false" />
    </property>

With this mapping I am able to insert a date into the table that shows up like this when I run a query in SSMS: 2018-05-24 10:48:17.000000 +00:00. However, when I try to query this table I get an two exceptions saying:

FormatException: Input string '5/24/2018 10:48:17 +00:00' was not in the correct format.

and

InvalidCastException: Unable to cast object of type 'System.DateTimeOffset' to type 'System.IConvertible'.

Does anyone know a way to have NHibernate recognize the format of the datetimeoffset column without changing the sql-type in the mapping? Or is there a sql-type that I can use in the mapping that will work for both the Oracle and SQL Server column types?

Not sure about .NET, but in Java with Hibernate/JPA I would try to use DATE (it includes time with seconds in Oracle) or TIMESTAMP without time zone and store the dates in UTC, possibly with a separate column for the original time zone. That should work, though perhaps you need to use timestamp with time zone in your database? — ewramner
I need to keep the time zone and adding a time zone column would require more changes to the app code than I would like. I am trying not to change the original Oracle DB but would consider it if there was another column type that stored the same amount of info as the TIMESTAMP WITH TIME ZONE type (date, time, and time zone). — Chris
In that case you probably need to write a user type, stackoverflow.com/questions/9471940/…. — ewramner

Frédéric Frédéric · Accepted Answer · 2018-05-29T17:22:58

The DateTimeType NHibernate type expects to receive a .Net DateTime from the datareader, but SqlClient does yield a .Net DateTimeOffset when reading a SQL Server DateTimeOffset. Such a SQL type should be mapped with a .Net DateTimeOffset, using a DateTimeOffset NHibernate type.

(Yes, the error message you receives is a bit misleading, it is emitted by catching the failure of Convert.ToDateTime(rs[index]) where rs is a DbDataReader. In your case, with SQL-Server, rs[index] yields a DateTimeOffset which fails the conversion. The message should not mention string indeed.)

Since this was working with Oracle, I guess this means your Oracle client yields a DateTime when reading a timestamp with time zone type. This is quite unfortunate since this means losing the offset, but also this means DateTimeOffset .Net type cannot be supported with Oracle. (Well, the Entity Framework Oracle adapter seems to support it, so I guess it is still doable by reaching the Oracle specific DbDataReader implementation. But it is not doable by using just what DbDataReader exposes.)

So the trouble is that mismatch between Oracle and Sql-Server clients about types with a time zone/offset, the first yielding a .Net DateTime (by the way then, what purpose serves the offset in your application since the Oracle client does not transmit it?), the second yielding a .Net DateTimeOffset.

If, like in your example, you store everything in UTC, maybe should you consider declaring your column as a simple datetime2(6) on SQL-Server side.
If you need to retrieve it with its Kind correctly set as Utc, then additionally map it with NHibernate type UtcDateTime.

Otherwise, as written by ewramner, you need to use a custom user type (a class implementing NHibernate.UserTypes.IUserType). His link is about a user type for handling the .Net DateTimeOffset with Oracle. But in your case you would want instead to write a user type class with a NullSafeGet method checking what the data-reader does yield and converting it to DateTime if needed. Then in your mapping use it by supplying its assembly qualified name as the property type. (See a simple user type implementation here.)

NHibernate Date Format Issue for Converted Database

1 Answers