C# DateOnly does not work with SQLServer date field

[IvoTops]

If a C# variable is defined as the new DateOnly type and the database column is Date using the column in a NPOCO Query will throw the error that the value cannot be casted to DateTime.

Is implementing support for DateOnly planned already?

[schotime]

You could try this for instance and see if that works.

var dbType = new SqlServer2012DatabaseType();
dbType.AddTypeMap(typeof(DateOnly), DbType.Date);
var db = new Database("connstring", dbType, SqlClientFactory.Instance);

[IvoTops]

This unfortunately does not work, an error is thrown when inserting a DateOnly field into the Date column in SQLServer;

System.InvalidCastException HResult=0x80004002 Message=Failed to convert parameter value from a DateOnly to a DateTime. Source=Microsoft.Data.SqlClient StackTrace: at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) at Microsoft.Data.SqlClient.SqlParameter.GetCoercedValue() at Microsoft.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) at Microsoft.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters, Boolean includeReturnValue) at Microsoft.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at NPoco.Database.<>c__DisplayClass296_0.b__0() in C:\TOP_REPOS\NPoco\src\NPoco\Database.cs:line 1701

This exception was originally thrown at this call stack: [External Code]

Inner Exception 1: InvalidCastException: Object must implement IConvertible.

[nglessner]

You can add a custom mapper definition to support this.

public class CustomMapper : DefaultMapper
{
    private static readonly Type DateOnlyType = typeof(DateOnly);
    private static readonly Type DateTimeType = typeof(DateTime);

    public override Func<object, object> GetFromDbConverter(Type destType, Type sourceType)
    {
        if (destType == DateOnlyType && sourceType == DateTimeType)
        {
            return src => { return DateOnly.FromDateTime((DateTime)src); };
        }
        return base.GetFromDbConverter(destType, sourceType);
    }
}

[IvoTops]

Thanks. I needed to add this also

  public override Func<object, object> GetToDbConverter(Type destType, MemberInfo sourceMemberInfo)
        {
            if (destType == DateOnlyType)
            {
                return src => { return ((DateOnly)src).ToDateTime(default); };
            }
            return base.GetToDbConverter(destType, sourceMemberInfo);
        }

[schotime]

I believe this has been added in https://www.nuget.org/packages/Microsoft.Data.SqlClient from 5.1.0.