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" ***@***.***> 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 <#571 (comment)>, 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.

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

works:

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

_connection
 .Fetch<EntityWithDate>(where: "", param: null)
 .First()
 .Kind; //Unspecified, _connection is a IDbConnection