CODEWITHSUNDEEP
c#t-sqldatetimeutc
hIpPy
hIpPy

Reputation: 5125

Reading DateTime saved in UTC from T-Sql database in C#

I'm saving datetime in t-sql db as UTC and not local time i.e after saving, it loses that it is UTC date ex: 2011-11-08 00:00:00.000. While reading from db in C#, it's read as local time and not UTC. ex: after reading the dateTime value, a dateTime.ToUniversalTime() gives a different value.

How do I read the db datetime value as UTC and not local time? Or I should saved in local time in t-sql?

Upvotes: 22

Views: 10442

Answers (3)

Xin
Xin

Reputation: 36590

Property Kind Can be: Local, UTC, Unspecified. If you just initialize a new DateTime object, the Kind is default set to Local.

// read exact same time from DB, but leave the 'Kind' to be Unspecified
// PS Kind is not local, not utc here
(DateTime)(reader["dbdate"]) 

// read exact same time from DB, but set 'Kind' to be UTC
// if your date in your DB is also utc time, then use this one
DateTime.SpecifyKind((DateTime)(reader["dbdate"]), DateTimeKind.Utc)

Upvotes: 0

orad
orad

Reputation: 16084

Jon Skeet's solution didn't work for me. For some reason DateTime.SpecifyKind(date, DateTimeKind.Utc) returns a DateTime object of Unspecified kind.

Following worked, however:

var date = DateTime.Parse("2017-03-07 00:15:2.663"); // Assuming UTC, Kind=Unspecified
date = TimeZoneInfo.ConvertTimeToUtc(date, TimeZoneInfo.Utc); // Assuming UTC, Kind=Utc
var output = Newtonsoft.Json.JsonConvert.SerializeObject(date); // "2017-03-07T00:15:02.663Z"

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1502616

Okay, if you're getting a DateTime with a Kind of Unspecified, you can just use:

DateTime utc = DateTime.SpecifyKind(unspecified, DateTimeKind.Utc);

This sort of problem is just one of the reasons I dislike DateTime.

Upvotes: 33

Related Questions