Reading nullable DateTime fails when the column has a non-null value

[ngeor]

I'm using MySql database and the NuGet version of Dapper. I'm reading from the database like this:

return _connection.Query<Person>(
    "SELECT * FROM Person WHERE PersonId=@PersonId",
    new { PersonId = 42 }).SingleOrDefault();

The class contains a nullable DateTime property. When the value is NULL in the database, it works fine. When it has a value, it fails complaining:

Error parsing column 14 (LastCommentedAt= - Object)

I managed to solve this by installing my own TypeHandler for DateTime?. I saw how Dapper.NodaTime is doing it so I created my own handler:

public class NullableDateTimeHandler : SqlMapper.TypeHandler<DateTime?>
{
    protected NullableDateTimeHandler()
    {
    }

    public static readonly NullableDateTimeHandler Default = new NullableDateTimeHandler();

    public override void SetValue(IDbDataParameter parameter, DateTime? value)
    {
        if (value.HasValue)
        {
            parameter.Value = value.Value;
        }
        else
        {
            parameter.Value = DBNull.Value;
        }
    }

    public override DateTime? Parse(object value)
    {
        if (value == null)
        {
            return null;
        }

        if (value is DateTime)
        {
            return (DateTime)value;
        }

        return Convert.ToDateTime(value);
    }
}

and I make sure I install this handler before anything else happens:

SqlMapper.AddTypeHandler(NullableDateTimeHandler.Default);

With this in place, I can read nullable dates correctly whether they have a NULL value or an actual value. I think this should be built-in functionality provided by Dapper, so I'm raising this ticket.

The stacktrace I get without my workaround looks like this:

MESSAGE:
System.Data.DataException : Error parsing column 14 (LastCommentedAt= - Object)
  ----> System.InvalidCastException : Cannot cast from source type to destination type.
+++++++++++++++++++
STACK TRACE:
at Dapper.SqlMapper.ThrowDataException (System.Exception,int,System.Data.IDataReader,object) <0x002e7>
at (wrapper dynamic-method) object.Deserializedf762666-acf8-4d08-832f-f40f4dc76cd7 (System.Data.IDataReader) <0x0105e>
at Dapper.SqlMapper/<QueryImpl>d__61`1<Person>.MoveNext () <0x006c3>
at System.Collections.Generic.List`1<Person>.AddEnumerable (System.Collections.Generic.IEnumerable`1<Person>) <0x0008f>
at System.Collections.Generic.List`1<Person>..ctor (System.Collections.Generic.IEnumerable`1<Person>) <0x000d5>
at System.Linq.Enumerable.ToList<Person> (System.Collections.Generic.IEnumerable`1<Person> <0x0004b>
at Dapper.SqlMapper.Query<Person> (System.Data.IDbConnection,string,object,System.Data.IDbTransaction,bool,System.Nullable`1<int>,System.Nullable`1<System.Data.CommandType>) <0x001fb>
at PersonDataLayer.Read (int) <0x0007f>
at PersonTests.TestCreate () <0x004ef>

--InvalidCastException
at System.Nullable`1<System.DateTime>.Unbox (object) <0x00081>
at (wrapper dynamic-method) object.Deserializedf762666-acf8-4d08-832f-f40f4dc76cd7 (System.Data.IDataReader) <0x00f3d>

[johandanforth]

Is this a MySql only issue, because I've never seen this problem using sql server or sql ce?

[jkrause098]

From my perspective it very well could be. I've yet to run into it on any of the SQL Server environments I've used Dapper on.

[peterres]

I had the same problem working with mysql and nullable DateTime. I had this in my connection string:

Convert Zero Datetime=True;Allow Zero Datetime=true;

and I removed the second part

Allow Zero Datetime=true;

to make the error go away. I could do that because I don't need that setting, so maybe it's not that simple for you.

[NickCraver]

@ngeor Can you let us know if what @peterres describes is the cause for you?

[ngeor]

Hi guys,

I had the exact same settings on my connection string. I took out the "Allow Zero Datetime=true", seems that this solves it.

Additionally, if I look at the documentation https://dev.mysql.com/doc/connector-net/en/connector-net-connection-options.html , I don't think "Convert Zero Datetime" and "Allow Zero Datetime" should be used at the same time as they seem to be doing different things.

In the mean time, I upgraded Dapper and Mysql to the latest NuGet packages and the error still happens when "Allow Zero Datetime" is present.

For my personal case, adapting the connection string is acceptable.

Thanks for the help!

[mgravell]

I would be interested in solving the problem more generally. I'll have a look at how the existing code differed, and what we can do here. I agree the right thing is to just work.

[mgravell]

Added test rig with all 4 permutations; it worked each time. Cannot reproduce. Could do with some help seeing this.

[ShadyAbuKalam]

I can assure that problem occurs with Sqlite v3 & Dapper 1.42 & 1.52.

I have two properties on my model to the same column

    [Column("datetime")]
    public DateTime? ArrivalTime { get; set; }

    [Column("datetime")]
    public DateTime ArrivalTime2 { get; set; }

ArrivalTime always returns null and ArrivalTime2 returns the date or '1-1-1-12:00AM` in case of null

[dopmr]

I have the same problem has @ShadyAbuKalam and i don't find a solution.

[erikpowa]

@ShadyAbuKalam
about why the "DateTime" returns an actual date Link short explanation

[ngeor]

Cannot reproduce with MySQL.Data 6.10.6 and Dapper 1.50.4 on .NET Framework 4.6.2

[0xced]

I think I finally found an easy solution to make dates just work with MySQL in #1916.