When converting a datetime2 data type to a datetime data type, an out-of-range value is returned.

Problem

I’ve got a datatable with 5 columns, where a row is being filled with data then saved to the database via a transaction.

An error occurs during the saving process:

As written, it suggests that my datatable is of type DateTime2 and my database is of type DateTime; this is incorrect.

The date column is set to a DateTime in the following format:

new DataColumn(“myDate”, Type.GetType(“System.DateTime”))

Question

Is this something that can be fixed with code or does anything need to be altered on the database level?

Asked by Gerbrand

Solution #1

If you don’t assign a value to a DateTime field that doesn’t tolerate NULL values, this can happen.

That was the solution for me!

Answered by andyuk

Solution #2

DATETIME and DATETIME2 both map to System. In.NET, you can’t truly do a “conversion” because they’re both the same.NET type.

See http://msdn.microsoft.com/en-us/library/bb675168.aspx for further information.

Can you indicate which of the two “SqlDbType” values you want for these two in your DataColumn definition?

However, the date range provided by SQL Server is significantly different.

DATETIME supports 1753/1/1 to “eternity” (9999/12/31) and DATETIME2 supports 0001/1/1 to “eternity” (9999/12/31).

So what you really need to do is check the date’s year; if it’s before 1753, you’ll need to modify it to anything AFTER 1753 in order for SQL Server’s DATETIME column to work with it.

Marc

Answered by marc_s

Solution #3

I had a DateTime column marked as not nullable in my SQL Server 2008 database, but its default value was the GetDate() method. I encountered this problem when using EF4 to insert a new object since I wasn’t explicitly giving a DateTime attribute on my object. I expected the SQL function to handle the date for me but it did not. My solution was to send the date value from code instead of relying on the database to generate it.

obj.DateProperty = DateTime.now; // C#

Answered by Graham

Solution #4

It was because of the datetime for me.

In this scenario, you’ll want to add null to your EF DateTime Object… as an example, look at my FirstYearRegistered function.

DateTime FirstYearRegistered = Convert.ToDateTime(Collection["FirstYearRegistered"]);
if (FirstYearRegistered != DateTime.MinValue)
{
    vehicleData.DateFirstReg = FirstYearRegistered;
}  

Answered by JGilmartin

Solution #5

I couldn’t get enough of this one. I didn’t want to use a date time that could be null (DateTime?). I didn’t have the option of using the datetime2 type in SQL Server 2008.

modelBuilder.Entity<MyEntity>().Property(e => e.MyDateColumn).HasColumnType("datetime2");

I finally decided on the following:

public class MyDb : DbContext
{
    public override int SaveChanges()
    {
        UpdateDates();
        return base.SaveChanges();
    }

    private void UpdateDates()
    {
        foreach (var change in ChangeTracker.Entries<MyEntityBaseClass>())
        {
            var values = change.CurrentValues;
            foreach (var name in values.PropertyNames)
            {
                var value = values[name];
                if (value is DateTime)
                {
                    var date = (DateTime)value;
                    if (date < SqlDateTime.MinValue.Value)
                    {
                        values[name] = SqlDateTime.MinValue.Value;
                    }
                    else if (date > SqlDateTime.MaxValue.Value)
                    {
                        values[name] = SqlDateTime.MaxValue.Value;
                    }
                }
            }
        }
    }
}

Answered by sky-dev

Post is based on https://stackoverflow.com/questions/1331779/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-results-out-of-range