The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range?

Question

I am working on application contains a datepicker and if I set the time in that picker to a very old value or far in the future when I try to save this value in the database the server throw this exception, what is the cause of it?

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

Solution 1

DateTime has the range: January 1, 1753, through December 31, 9999

DateTime2 has the range: 0001-01-01 through 9999-12-31

So if you are entering a date before 1753 you would get this error when the field in the table is of type DateTime.

Solution 2

Entity Framework will add default date for the field of value {01/01/0001 00:00:00} and this is outside the range of SQL Date Generation. So to make it work, we need to ask EF not to generate a default date, we can make it nullable by doing the following in the Model of that class.

In this case a default value is generated for the LastLoggedIn field by EntityFramework. If this field in the datebase can take null value, implies we can make it nullable by doing the follwoing in the model

 [Display(Name = "LastLoggedIn")]
        public DateTime? LastLoggedIn { get; set; }

Solution 3

Products prd = new Products();            
prd.CreatedDate = DateTime.Now;

You can add as above