EF Core cannot store DateTime on local DB but not on Azure DB

Question

I want to store an object using EF Core on a SQL Database hosted in Azure. The code works fine on the dev DB of version Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64). The same code throws an error on the Azure hosted SQL DB of Version Microsoft SQL Azure (RTM) - 12.0.2000.8

The first error message is

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

I tried to fix it by forcing the datetype on the column DateTime like this

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        
        var edoProp = modelBuilder.Entity<TEingangsrechnungsdokumente>();
        edoProp.Property(x => x.DAngelegtAm).HasColumnType("DateTime");
        edoProp.Property(x => x.DLetzteAenderungAm).HasColumnType("DateTime");
    }

After adding this code, the error message now read

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

All DateTime Fields in the object carry values

The values are set like this:

var recordDate = System.DateTime.Now;

egrDoc.ODateitext = recordText;
egrDoc.OEingangsrechnungFK = RecordId;
egrDoc.ODateiname = Filename;
egrDoc.ODateierweiterung = System.IO.Path.GetExtension(Filename);
egrDoc.ODatei = FileBytes;

egrDoc.DAngelegtAm = recordDate;
egrDoc.DAngelegtVonFk = UserId;
egrDoc.DLetzteAenderungAm = recordDate;
egrDoc.DLetzteAenderungVonFk = UserId;

The model is defined like this

[Key]
[Column("d_EDO_ID")]
public int DEdodId { get; set; }

[Column("o_Eingangsrechnung_FK")]
public int OEingangsrechnungFK { get; set; }

[Column("o_Dateiname")]
public string ODateiname { get; set; } = null!;

[Column("o_Dateierweiterung")]
public string ODateierweiterung { get; set; } = null!;

[Column("o_Datei")]
public byte[] ODatei { get; set; } = null!;

[Column("o_Dateitext")]
public string ODateitext { get; set; } = null!;

[Column("d_AngelegtAm")]
public DateTime DAngelegtAm { get; set; }

[Column("d_AngelegtVon_FK")]
public int DAngelegtVonFk { get; set; }

[Column( "d_LetzteAenderungAm")]
public DateTime DLetzteAenderungAm { get; set; }

[Column("d_LetzteAenderungVon_FK")]
public int DLetzteAenderungVonFk { get; set; }

Edit:

The datatype cannot be changed to DateTime2.

The Question does not answer the question.

The database on the local machine in a restore of the azure database.

Use `datetime2` instead of `datetime` or remove `.HasColumnType("DateTime")` completely. The error complains about the database types, not the .NET DateTime type. `datetime` is a legacy type replaced by `datetime2` since 2005-ish — Panagiotis Kanavos, Feb 21 '23 at 09:56
The error doesn't complain about `2023-02-20` either. It complains because an attempt was made to cast a `datetime2` value to a `datetime` that's out of normal range. Are you using `0001-01-01` as a default value in the database instead of `null` ? — Panagiotis Kanavos, Feb 21 '23 at 09:59
Have you tried searching internet for "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."? It will bring this as the first result: https://stackoverflow.com/questions/1331779/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-results-out-of-range — Alex, Feb 21 '23 at 10:01
Does this answer your question? [Conversion of a datetime2 data type to a datetime data type results out-of-range value](https://stackoverflow.com/questions/1331779/conversion-of-a-datetime2-data-type-to-a-datetime-data-type-results-out-of-range) — Alex, Feb 21 '23 at 10:01
In other words check your db tables, they most likely have different column data types: one has `datetime2` and the other `datetime` or something along those lines. — Alex, Feb 21 '23 at 10:02

EF Core cannot store DateTime on local DB but not on Azure DB

0 Answers0