Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

Question

This is my simple code for database insertion. But when am trying to insert to datetime column in the database, I got the error:

Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

I tried some marked as answers here but none of those worked for me, I am using ASP.NET Core 5 MVC.

public void rfrPost()
{
    nfmsdb.request_for_refund.Add(new request_for_refund
                                      {
                                          date_requested = date_requested
                                      });
    
    nfmsdb.SaveChanges();
}

My model in edmx:

public partial class request_for_refund
{
    public DateTime date_requested { get; set; }
}

My column datatype in the database:

date_requested DateTime Nullable Yes

Thank you

Answer 1

I think this error occurs because the range of datetime and datetime2 is different. I think there is a detailed answer here.

Both the DATETIME and DATETIME2 map to System.DateTime in .NET - you cannot really do a "conversion", since it's really the same .NET type.

See the MSDN doc page: http://msdn.microsoft.com/en-us/library/bb675168.aspx

There are two different values for the "SqlDbType" for these two - can you specify those in your DataColumn definition?

BUT: on SQL Server, the date range supported is quite different.

DATETIME supports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2 supports 0001/1/1 through eternity.

So what you really need to do is check for the year of the date - if it's before 1753, you need to change it to something AFTER 1753 in order for the DATETIME column in SQL Server to handle it.