NHibernate Sqldatetime must be between 1/1/1753 and 12/31/9999

Question

I'm using NHibernate in my MVC project. My problem is, while i m trying to update an object i m getting following error.

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

In debug mode I see the date property is not null. I set Datetime nullable on mapping. But I'm still getting sqldatetime error.

public class EntityBaseMap<T> : ClassMap<T> where T : EntityBase
{
    public EntityBaseMap()
    {
        Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.IsDeleted).Not.Nullable();
        Map(x => x.CreatedAt).Nullable();
        Map(x => x.UpdatedAt).Nullable();
        Map(x => x.Random).Formula("NEWID()");

        References(x => x.Status).Column("StatusId");

        Where("IsDeleted=0");
    }
}

Datetime properties are not null on save.

public int SaveOrUpdatePage(PageDto PageDto)
{
    var page = PageDto.Id > 0 ? ById<Page>(PageDto.Id) : new Page();
    var status = PageDto.Status.Id > 0 ? LookupById<Status>(PageDto.Status.Id) : null;
    var type = PageDto.Type.Id > 0 ? LookupById<PageType>(PageDto.Type.Id) : null;
    //var parent = PageDto.Parent.Id > 0 ? ById<Page>(PageDto.Parent.Id) : page.Parent;

    page.Description = PageDto.Description;
    page.Title = PageDto.Title;
    page.SpotText = PageDto.SpotText;
    page.Status = status;
    page.Text = PageDto.Text;
    page.Url = !string.IsNullOrEmpty(PageDto.Url) ? PageDto.Url.ToFileName() : PageDto.Title.ToFileName();
    page.Featured = PageDto.Featured;
    page.Type = type;

    using (var tran = UnitOfWork.CurrentSession.BeginTransaction())
    {
        UnitOfWork.CurrentSession.SaveOrUpdate(page);
        tran.Commit();
    }


    page.CreateDirectory();

    if (PageDto.Id == 0)
    {
        page.Copy();
        page.CopyThumbs();
    }

    SetResultAsSuccess();

    return page.Id;
}

I m using SQLServer 2008 and on table datetime columns check with allow null.

Answer 1

This is not an issue of NULL or NULLABLE column. This is an issue of the C# default value of the DateTime ValueType.

default(DateTime) == new DateTime(1,1,1) // 1st January of year 1 

So, because the CreatedAt or UpdatedAt is defined as

public virtual DateTime CreatedAt { get; set; }
public virtual DateTime UpdatedAt { get; set; }

And never set to other than its default value ... the which value is 1.1.0001. And this is less then SQL Server lower bound 1.1.1753...

Other words, be sure that you set these values to some meaningful values, e.g. DateTime.Now