I have an Sql database which contains a date field.
I use Dapper to update the database as follows:
const string sql = "UPDATE AdminDb.Users " +
"SET IsLoggedOn = 1, LastLoggedOn = @LastLoggedOn " +
"WHERE Username = @username";
var date = DateTime.UtcNow;
DatabaseConnectionBase.DatabaseConnection.Execute(sql, new { username, LastLoggedOn = date });
I am finding to my great annoyance when breaking before the actual update, the date variable reads 30/3/2015 9:32:54 however when I run the update the database saves the date as 30/3/2015 10:32:54
As the UK yesterday changed from GMT to BST (UTC +1) I am sure that the database seems to be trying to compensate for this as this issue never came up before.
I thought I had averted this sort of issue by using the DateTime.UtcNow property to save my date.
This is causing serious issues when validating users.
As per a suggestion from another site I tried formatting the date as follows:
var date = DateTime.UtcNow.ToString("o");
The intention being to force the date into ISO-8601 format but I had no luck with that.
Has anyone got any ideas?
This happened to me too. What I did was serialize the datetime to a string myself before adding it as a parameter.
internal const string DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
cmd.Parameters.Add("@Threshold", DbType.DateTime).Value = threshold.ToString(DateTimeFormat);
If you do the same thing with pure ADO.NET, does the same thing happen? I wonder if this is a database thing or a provider thing, rather than a library thing. Dapper has to ToLocalTime()
or ToUniversalTime()
calls - it passes the time through unaltered. On SQL Server, the following works fine in a BST setting:
public void SO29343103_UtcDates()
{
const string sql = "select @date";
var date = DateTime.UtcNow;
var returned = connection.Query<DateTime>(sql, new { date }).Single();
var delta = returned - date;
Assert.IsTrue(delta.TotalMilliseconds >= -1 && delta.TotalMilliseconds <= 1);
}