Dapper: DateTime columns from database have Kind property Unspecified.

[moxplod]

I setup a simple unit test that adds a row to the database and the date that is added to the database has Kind property set when inserting it as Utc by default. As its added using DateTime.UtcNow.

When i get the same object back from Database using dapper the same field has the correct datetime but the Kind property is Unspecified - when it should be Utc.

[mgravell]

The value in the database is just a number; it doesn't have any flags to
indicate whether it is UTC or otherwise. It _doesn't know_. So
"unspecified" is the only reliable value that can be populated - although
actually, I think it is the ADO.NET provider that is doing this, not dapper
specifically. I strongly suspect you'd see exactly the same thing if you used ADO.NET directly.

Basically: while System.DateTime (in .NET-land) knows what "kind" it is,
"datetime" (in SQL-Server-land) _does not_.

[RehanSaeed]

Better to use the DateTimeOffset in C# and DATETIMEOFFSET in T-SQL in this case. The Offset will come back as 00:00 which is UTC.

[ChuckPG]

@moxplod add DateTimeKind=Utc; to your ADO.net connection string and all your dates will be correctly UTC on read.

[moxplod]

Wow if that works that would be great! Will try it soon.

On Jan 6, 2017 4:54 AM, "ChuckPG" [e-mail] wrote:

@moxplod https://github.com/moxplod add DateTimeKind=Utc; to your
ADO.net connection string and all your dates will be correctly UTC on read.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/571#issuecomment-270787618,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AEV-myUhDaqrImqSRe1r9TKDlbPg4DRMks5rPXuhgaJpZM4JU0db
.

[kwaazaar]

@ChuckPG That's not true. Tried that for both MS Sql and MySql, but both complain the DateTimeKind-parameter is not supported.

Specifying a custom TypeHandler on SqlMapper will do the job, like mentioned here.

[ChuckPG]

@kwaazaar you are right, I was using the Sqlite provider.

For what its worth here's it in their changelog for sqlite:
https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki
Added DateTimeKind at version 1.0.77.0

[MNie]

@mgravell any update on this? I hit the same issue in my project. Everything works ok, when I use SqlConnection + SqlCommand to get data, but when I use Fetch<Entity>, retrieved DateTime has always
unspecified kind.

```c#
internal class EntityWithDate
{
public virtual DateTime Date { get; set; }
}

works:
```c#
var cmd = _connection.CreateCommand();
cmd.CommandText = "Select [Date] from EntityWithdate";
var rd = cmd.ExecuteReader();
DateTime result;
while (rd.Read())
{
    result = rd.GetDateTime(0).Kind; //UTC
}

doesn't works
c# _connection .Fetch<EntityWithDate>(where: "", param: null) .First() .Kind; //Unspecified, _connection is a IDbConnection