Parse date time c# with correct timezone and kind

Question

I have a datetime in database which I read using SqlDataReader and then cast it to (DateTime). After the cast its Kind property is DateTimeKind.Unspecified.

Then I have another string which I read from some other source. Its format is like this 2016-01-20T22:20:29.055Z. I do DateTime.Parse("2016-01-20T22:20:29.055Z") and its Kind property is DateTimeKind.Local.

How do I properly parse the both date times for comparison? Do I need to use DateTimeOffsets? How should I parse them?

Thanks

Answer 1

Because SQLReader cannot reasonably infer a DateTimeKind, it leaves it as unspecified. You'll want to use DateTime.SpecifyKind to change the DateTimeKind on your output from the SQLReader to the appropriate value. This works ok if you are only dealing with UTC and one consistent local time zone; otherwise, you really should be using DateTimeOffset in both your code and the SQL Database.

The string "2016-01-20T22:20:29.055Z" is ISO 8601 compliant and is a UTC date; however, DateTime.Parse with only 1 argument can end up performing a conversion to local time. Per the documentation:

Generally, the Parse method returns a DateTime object whose Kind property is DateTimeKind.Unspecified. However, the Parse method may also perform time zone conversion and set the value of the Kind property differently, depending on the values of the s and styles parameters:

• If s contains time zone information, the date and time is converted to the time in the local time zone and the Kind is DateTimeKind.Local.
• If s contains time zone information, and styles includes the AdjustToUniversalflag, the date and time is converted to Coordinated Universal Time (UTC) and the Kind is DateTimeKind.Utc.
• If s contains the Z or GMT time zone designator, and styles includes the RoundtripKind flag, the date and time are interpreted as UTC and the Kind is DateTimeKind.Utc.

Also see UTC gotchas in .NET and SQL Server in Derek Fowler's blog for additional coverage on the topic.

Answer 2

In your second example, 2016-01-20T22:20:29.055Z has timezone information provided with it; the 'Z' at the end indicates that the timestamp is intended for Coordinated Universal Time (UTC). However, DateTime.Parse() will default its conversion using DateTimeKind.Local unless a specific timezone is specified. You can use DateTime.ParseExact to be more specific.

As to why the datetime values in your database are coming out as Unspecified, that's likely because they contain no timezone indication at all. Check to see if your database values specify timezone information, either by using 'Z' at the end or specifying an exact timezone, such as 2016-01-20T22:20:29.055-07:00 (UTC-7).

Answer 3

You can use something like this:

string format = "ddd dd MMM h:mm tt yyyy";
DateTime dateTime = DateTime.ParseExact(dateString, format,
    CultureInfo.InvariantCulture);

In format variable, you can put the format you want, and pass it to ParseExact function.

Hope it helps.

Answer 4

You are missing the datetime context (offset) in your database. You should persist it either in a datetimeoffset column or in a datetime column but persisting utc datetimes. And always better compare two utc datetimes.

Answer 5

I coded a quick C# console app that I pasted in below. This converts a UTC date and time to a string (format similar to the ISO 8601 format described in another post with some extra digits of precision), writes it to a file, reads it from the file (as a string) and then converts it back to a UTC date and time. It then compares the two UTC Date Time objects, which are both of UTC kind, and they match.

class Program
{
    // "2016-01-20T22:20:29.055Z" is ISO 8601 compliant and is a UTC date
    const string dtf = "yyyy-MM-ddTHH:mm:ss.fffffffZ";

    static void Main(string[] args)
    {
        string file = @"c:\temp\file.txt";
        DateTime dt = DateTime.UtcNow;

        
        using (var sw = new System.IO.StreamWriter(file))
        {
            sw.WriteLine(dt.ToString(dtf, System.Globalization.CultureInfo.InvariantCulture));
        }

        DateTime dtin;
        using (var sr = new System.IO.StreamReader(file))
        {
            dtin = DateTime.ParseExact(sr.ReadLine(), dtf, System.Globalization.CultureInfo.InvariantCulture);
        }

        Console.WriteLine(dt.ToString(dtf) + "\r\n" + dtin.ToString(dtf) + "\r\nEquality:" + (dt == dtin));
        Console.ReadLine();
    }
}