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

ngeor · 2015-06-06T19:43:35Z

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>

The text was updated successfully, but these errors were encountered:

johandanforth · 2015-06-07T17:45:51Z

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

jkrause098 · 2015-06-07T19:44:41Z

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 · 2015-10-19T11:02:32Z

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 · 2015-11-27T13:05:06Z

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

ngeor · 2015-11-28T06:27:43Z

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 · 2015-11-28T07:38:13Z

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 · 2015-11-29T12:27:26Z

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

ShadyAbuKalam · 2016-10-30T14:23:30Z

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 · 2017-01-31T15:24:53Z

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

erikpowa · 2017-01-31T20:34:01Z

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

As per issue DapperLib#295, setting "AllowZeroDateTime=True" in the connection string causes an InvalidCastException in SqlMapper.

* Don't create the connection in Dispose: this avoids creating a SQLConnection simply to Dispose it. * Run MySQL tests against the open MySqlConnection: 'connection' is a protected variable in the TestBase class that is a SQL Server connection, not a MySQL connection. * Skip broken MySQL tests: as per issue #295, setting "AllowZeroDateTime=True" in the connection string causes an InvalidCastException in SqlMapper.

ngeor · 2018-01-25T17:19:37Z

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

Before this commit, the following exception would be thrown: > System.InvalidCastException > Unable to cast object of type 'MySqlConnector.MySqlDateTime' to type 'System.Nullable`1[System.DateTime]'. > at Deserialize74d21eca-a78e-49dc-b635-d007c613cb51(DbDataReader ) Fixes DapperLib#295

0xced · 2023-06-13T14:03:23Z

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

NickCraver added the db:mysql label Oct 10, 2015

mgravell added a commit that referenced this issue Nov 29, 2015

Test rig for Issue #295

e918254

mguinness mentioned this issue Dec 10, 2015

InvalidCastException for Nullable<DateTime> on Mono (RC1) dotnet/efcore#4012

Closed

ImGonaRot mentioned this issue Apr 15, 2017

SqlMapper.TypeHandler<DateTime?> not working with SQLite #742

Open

bgrainger added a commit to bgrainger/Dapper that referenced this issue May 12, 2017

Skip broken MySQL tests.

88edda5

As per issue DapperLib#295, setting "AllowZeroDateTime=True" in the connection string causes an InvalidCastException in SqlMapper.

bgrainger mentioned this issue May 12, 2017

Fix MySQL tests #774

Merged

ngeor closed this as completed Jan 25, 2018

joangilabert mentioned this issue Aug 29, 2019

actualizacion desde el original (#1) #1319

Closed

0xced mentioned this issue Jun 13, 2023

Fix InvalidCastException when reading MySql DateTime #1916

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

ngeor commented Jun 6, 2015

johandanforth commented Jun 7, 2015

jkrause098 commented Jun 7, 2015

peterres commented Oct 19, 2015

NickCraver commented Nov 27, 2015

ngeor commented Nov 28, 2015

mgravell commented Nov 28, 2015

mgravell commented Nov 29, 2015

ShadyAbuKalam commented Oct 30, 2016 •

edited

dopmr commented Jan 31, 2017

erikpowa commented Jan 31, 2017 •

edited

ngeor commented Jan 25, 2018

0xced commented Jun 13, 2023

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

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

Comments

ngeor commented Jun 6, 2015

johandanforth commented Jun 7, 2015

jkrause098 commented Jun 7, 2015

peterres commented Oct 19, 2015

NickCraver commented Nov 27, 2015

ngeor commented Nov 28, 2015

mgravell commented Nov 28, 2015

mgravell commented Nov 29, 2015

ShadyAbuKalam commented Oct 30, 2016 • edited

dopmr commented Jan 31, 2017

erikpowa commented Jan 31, 2017 • edited

ngeor commented Jan 25, 2018

0xced commented Jun 13, 2023

ShadyAbuKalam commented Oct 30, 2016 •

edited

erikpowa commented Jan 31, 2017 •

edited