CODEWITHSUNDEEP
c#sql-serverdatetimedapper
Mr. Boy
Mr. Boy

Reputation: 63806

When querying DateTime Fields from the DB, can I control the DateTimeKind?

When I query a SqlServer database from C# which involves DateTime fields, the returned System.DateTime has Kind==Unspecified which is unsurprising as SqlServer DateTime type retains no timezone information.

I wondered if there's a way to automatically have these values read as local or universal, rather than manually convert them after reading the query results which introduces more possibility of mistakes when a field is missed.

Typical code looks like:

    using (var conn = ...)
    using (var command = ...)
    {
        conn.Open();
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
          DateTime dateField =  (DateTime)reader["date"];
          //dateField.Kind == Unspecified
        }
    }

Upvotes: 7

Views: 1549

Answers (1)

Igor
Igor

Reputation: 62258

DateTime dateField = DateTime.SpecifyKind((DateTime)reader["date"], DateTimeKind.Utc);

As its not persisted in the database you have to specify it on retrieval in your own code, there is no shortcut method in the DataReader although you could create an Extension method.

public static class Helper{
    public static DateTime GetDateTimeAsUtc(this IDataReader reader, string column){
        return DateTime.SpecifyKind((DateTime)reader[column], DateTimeKind.Utc);
    }
}

and then call it 

DateTime dateField = reader.GetDateTimeAsUtc("date");

Upvotes: 4

Related Questions