DateOnly.Min and DateTime2

ServiceStack.OrmLite
1

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

2

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.

3

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?

4

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

5

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?

6

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:

7

image
image871×128 9.41 KB 

            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?

8

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

9

I unfortunately have no idea what to do.

image
image871×128 9.41 KB

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

image
image957×406 49.8 KB

10

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.

11

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);
        }
    }
2 Likes
12

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