NHibernate: SqlDateTime overflow Issue on object Update

Posted by Zafar Ullah - zafarjcp@gmail.com | 4:53 AM | , | 0 comments »

Yesterday i come across NHibernate SqlDateTime overflow exception when ever i update my object. During debug i all datetime fields have there default values/entered values but when i Flush my session it throws this noughty exception :)

Data.SqlTypes.SqlTypeException with the message "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.".

after alot of findings finally i found the reason behind this. Actually I didn't map that column as a Nullable OR DateTime? but just as DateTime. and when ever NHibernate read the row from DB its default value is null which NHibernate remembers. When the Object is rehydrated by NHibernate the Date is set to the value DateTime.MinValue. When the Session is synchronized with the db, NHibernate assumes that something has changed, because the currentState and the previousState are different and tries to update the row. Wich in turn fails, because DateTime.MinValue won't fit into a SqlServer datetime column.

The solution:
Make your datetime nullable either by putting ? at end of Datetime like DateTime? or Nullable<datetime>

e.g

[Property(Name = "CreatedDate", Column = "CreatedDate", NotNull = false)]//NotNull=false means null is allowed.
private DateTime? _createdDate = DateTime.Now;

I prefer using DateTime? rather then Nullable because that leades to change the date type where ever used.

0 comments