DateOnly.Min and DateTime2

We have a strange issue with DateOnly.MinValue in SQL Server 2017 and 2019.

su.PassportExpiryDate = DateOnly.MinValue;

PassportExpiryDate is of type DateOnly. In the database this column is of type DateTime2.
Out of interest, we have set the following converter:
SqlServer2016Dialect.Provider.RegisterConverter<DateTime>(new SqlServerDateTime2Converter()); // map to DateTime2 sql type

However this gives us a SQL exception when we try save the object to the db.
System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The SQL that is being generated is:

"UPDATE SystemUser" SET "IsDeleted"=@IsDeleted, "CreatedDate"=@CreatedDate, "FirstName"=@FirstName, "Surname"=@Surname, "MobileNumber"=@MobileNumber, "EmailAddress"=@EmailAddress, "ModifiedDate"=@ModifiedDate, "LastLoggedIn"=@LastLoggedIn, "UtcOffset"=@UtcOffset, "ConfirmationCode"=@ConfirmationCode, "FirebaseInfo"=@FirebaseInfo, "PaymentTerm"=@PaymentTerm, "ProfileImageFileDataId"=@ProfileImageFileDataId, "ProfileImageThumbnailFileDataId"=@ProfileImageThumbnailFileDataId, "FlightMarkupOverride"=@FlightMarkupOverride, "Gender"=@Gender, "PreferredDepartureAirportIataCode"=@PreferredDepartureAirportIataCode, "PassportNationalityCode"=@PassportNationalityCode, "PassportExpiryDate"=@PassportExpiryDate, "PassportNumber"=@PassportNumber, "NationalityCode"=@NationalityCode, "DateOfBirth"=@DateOfBirth, "CallingCode"=@CallingCode, "InvoicingDetails"=@InvoicingDetails, "MarketingConsent"=@MarketingConsent, "LoanApplicationTimeoutExpiry"=@LoanApplicationTimeoutExpiry, "LoginAttempt"=@LoginAttempt, "LockoutEnd"=@LockoutEnd, "Notes"=@Notes, "EnableTSO"=@EnableTSO WHERE "SystemUserId"=@SystemUserIdPARAMS: @SystemUserId=4815, @PassportExpiryDate=1/1/0001 12:00:00 AM, ...

This also does not work (but should not be necessary): DateOnly.FromDateTime(DateTime.MinValue).

Not exactly sure why this is happening. Does the date converter for DateOnly need to handle this for SQL Server differently?

ServiceStack.OrmLite.SqlServer.Data 5.13.2 on net6

Unless I’m missing something? this is the standard SQL Server Date limitation, i.e. you can’t add a Date value lower than what SQL Server supports.

That is correct. However we often set normal DateTime objects to DateTime.Min which Ormlite maps correctly to DateTime2 in SQL.

Why would this not work for DateOnly.Min to DateTime2?

Because the SqlServerDateTime2Converter implementation you’ve only registered and only works for the DateTime Type. DateOnly is not the same type.

Correct. I figured as much.

How do we go about supporting DateOnly.Min/Max and TimeOnly.Min/Max in Ormlite such that it maps correctly in SQL Server to DateTime2?

Create and register a local copy of the SqlServerDateTime2Converter for DateOnly.

We’re still busy with a lot of post release tasks, if you want it as a default in OrmLite leave a feature request for it in our new GitHub Discussions Ideas area:

            SqlServer2016Dialect.Provider.RegisterConverter<DateOnly>(new SqlServerDateTime2Converter()); // map to DateTime2 sql type
            SqlServer2016Dialect.Provider.RegisterConverter<TimeOnly>(new SqlServerDateTime2Converter()); // map to DateTime2 sql type

I think a new converter will be needed?

Of course, they’re different Types. It needs a local copy modified to support each Type.

I unfortunately have no idea what to do.

System.InvalidCastException: ‘Unable to cast object of type ‘System.DateOnly’ to type ‘System.DateTime’.’

SqlServer2016Dialect.Provider.RegisterConverter<DateOnly>(new SqlServerDateOnlyConverter()); // map to DateTime2 sql type

The purpose of the converter is to convert from the .NET Type to/from the value the SQL Server expects, if it’s not clear leave a feature request (link above), I’ll look into it when I have free time again.

This seems to do the trick:

    public class SqlServerDateOnlyConverter : DateOnlyConverter
        public override string ColumnDefinition => "DATETIME2";

        const string DateTimeFormat = "yyyyMMdd HH:mm:ss.fffffff";

        public override DbType DbType => DbType.DateTime2;

        public override string ToQuotedString(Type fieldType, object value)
            return DateTimeFmt(((DateOnly)value).ToDateTime(TimeOnly.MinValue), DateTimeFormat);

        public override object FromDbValue(Type fieldType, object value)
            if (value is string str && DateOnly.TryParseExact(str, DateTimeFormat, null, DateTimeStyles.None, out var date))
                return date;

            return base.FromDbValue(fieldType, value);

Nice, thx for sharing - will look at adding to OrmLite in near future.