DateTime from SQL Server with timezone

Question

When I execute a query and access the value using DataReader and converting it to string, I don't get the TimeZone(2015-02-17T00:00:00).

But on creating a DataSet and then converting it to XML, I get the TimeZone in the DateTime field(2015-02-17T00:00:00+11:00).

The code to retrieve the data from the datareader is var dateTime = reader["dte_tme"].ToString() which yields 17/02/2015 12:00:00 AM (without TimeZone).

string dateTime = reader["dte_tme"].ToString();
DateTime dt = Convert.ToDateTime(dateTime);

So I know that the field 'dte_tme' is a DateTime field which may not always have a value. I'm converting it to a string and then converting it back to DateTime. The value of dt is then serialized into a json. And the output that I get is 2015-02-17T00:00:00 instead of 2015-02-17T00:00:00+11:00. I checked the TimeZone of dt and it is Unspecified.

The DateTime object that I create from the XML from DataSet has TimeZone as Local which serializes to 2015-02-17T00:00:00+11:00.

Why is this inconsistency?

Also, is there a way to get the DateTime with TimeZone using DataReader?

My ultimate goal is to serialize the DateTime field in ISO 8601 format.

Answer 1

This is a very common anti-pattern:

string dateTime = reader["dte_tme"].ToString();
DateTime dt = Convert.ToDateTime(dateTime);

The correct incantation is as follows:

DateTime dt = (DateTime) reader["dte_tme"];

While the return type of reader["dte_time"] is an object, that object contains a DateTime. If you set a breakpoint, you'd see the DateTime is already there. You just need to cast it so it can be assigned to a DateTime variable. This is called unboxing.

If the datetime column in the SQL database is nullable, then you should test for that like this:

DateTime? dt = reader["dte_tme"] == DBNull.Value ? null : (DateTime) reader["dte_tme"];

Or sometimes you will see it like this, which is equally acceptable:

DateTime? dt = reader["dte_tme"] as DateTime?;

It absolutely does not need to be treated as a string at any point when retrieving it from the database. If it's a datetime in the database, then it's a DateTime in C#.

You should use a casting operation when pulling data from a datareader, even with other data types such as integers, decimals, and even strings. You can see other type mappings between SQL Server data types and .NET data types in the chart here.

Now with regard to time zone, that's a different issue. First, understand that DateTime doesn't keep a time zone. It only has knowledge of the DateTimeKind it is assigned. By default, the kind is Unspecified, which essentially means, "I don't know; it could be anything".

That said, different protocols have different requirements. JSON has no predefined format for dates, but the most common convention (and best practice) is to store a date in ISO8601 format, which is YYYY-MM-DDTHH:mm:ss. Time zone information is optional, and will usually not be included when the .Kind of a DateTime is DateTimeKind.Unspecified. If it were Utc, then you would see a Z at the end, and if it were Local, then you would see an offset of the local time zone, such as +11:00. That is, whatever offset is appropriate for that time zone, at that particular moment. An offset is not the same thing as a "time zone", because different offset could apply within the same time zone at different times - usually to daylight saving time.

XML is a bit different. Most of the XML serialization in .NET will use the W3C XML Schema specification, and will map a DateTime to an xsd:dateTime type. How exactly it is rendered will depend on the Kind.

• For DateTimeKind.Unspecified, it will not include an offset.
• For DateTimeKind.Utc, it will append a Z
• For DateTimeKind.Local, it will append the local offset

You asked why the Kind is Local when you look at it in the dataset? That's because DataSet has an ugly behavior of assuming that all times are local. It essentially ignores the .Kind property and assumes the behavior of DateTimeKind.Local. This is a longstanding bug.

Ideally, you would use a datetimeoffset type in SQL Server, and a DateTimeOffset type in .NET. This avoids the "kind" issues, and serializes nicely in JSON (when you use modern serializers like JSON.NET). In XML, however, it should get mapped to xsd:dateTime and rendered just like the local DateTime did, just with the correct offset. However it instead ends up looking like this:

<Value xmlns:d2p1="http://schemas.datacontract.org/2004/07/System">
    <d2p1:DateTime>2015-03-18T03:34:11.3097587Z</d2p1:DateTime>
    <d2p1:OffsetMinutes>-420</d2p1:OffsetMinutes>
</Value>

That's with DataContractXmlSerializer. If you use the XmlSerializer, you it can't render at all. You just get an empty node, such as <Value/>.

However, even with all of that said, you said you were using DataSet, and that comes with it's own set of behaviors. On the bad side, it will assume that all DateTime values have DateTimeKind.Local - even when they don't, as I mentioned above. Consider the following:

DataTable dt = new DataTable();
dt.Columns.Add("Foo", typeof (DateTime));

dt.Rows.Add(new DateTime(2015, 1, 1, 0, 0, 0, DateTimeKind.Unspecified));
dt.Rows.Add(new DateTime(2015, 1, 1, 0, 0, 0, DateTimeKind.Local));
dt.Rows.Add(new DateTime(2015, 1, 1, 0, 0, 0, DateTimeKind.Utc));

DataSet ds = new DataSet();
ds.Tables.Add(dt);
string xml = ds.GetXml();

Debug.Write(xml);

This is the output when I run it (in the US Pacific time zone):

<NewDataSet>
  <Table1>
    <Foo>2015-01-01T00:00:00-08:00</Foo>
  </Table1>
  <Table1>
    <Foo>2015-01-01T00:00:00-08:00</Foo>
  </Table1>
  <Table1>
    <Foo>2015-01-01T00:00:00-08:00</Foo>
  </Table1>
</NewDataSet>

However, the good news is that DateTimeOffset values are a little better:

DataTable dt = new DataTable();
dt.Columns.Add("Foo", typeof(DateTimeOffset));

dt.Rows.Add(new DateTimeOffset(2015, 1, 1, 0, 0, 0, TimeSpan.FromHours(11)));
dt.Rows.Add(new DateTimeOffset(2015, 1, 1, 0, 0, 0, TimeSpan.Zero));
dt.Rows.Add(new DateTimeOffset(2015, 1, 1, 0, 0, 0, TimeSpan.FromHours(-3)));

DataSet ds = new DataSet();
ds.Tables.Add(dt);
string xml = ds.GetXml();

Debug.Write(xml);

Output:

<NewDataSet>
  <Table1>
    <Foo>2015-01-01T00:00:00+11:00</Foo>
  </Table1>
  <Table1>
    <Foo>2015-01-01T00:00:00Z</Foo>
  </Table1>
  <Table1>
    <Foo>2015-01-01T00:00:00-03:00</Foo>
  </Table1>
</NewDataSet>

For the most part, this is correct, though technically it should have serialized the second one using +00:00 instead of Z, but that's not going to matter all that much in practice.

The last thing I'd just like to say is that in general, DataSet is a relic from the past. In modern development, there should be very little need to use it in your day to day code. If possible, I would seriously consider exploring other options.

Answer 2

It seems that DataSet.GetXml() and other xml-writing methods of DataSet share a ugly problem of assuming that the datetime value is a local time. It uses the timezone setting of the machine where the code executes.

The MS workarounds to fix it are equally ugly. From http://blogs.msdn.com/b/bclteam/archive/2005/03/07/387677.aspx :

DataSet is the hardest technology of the three to work around this problem. Some options:
1. Change the column types to be Int64 or String

2. Call DateTime.ToLocalTime on the DateTime before putting it in the DataSet and call DateTime.ToUniversalTime after taking it out. This will  effectively “cancel out” the adjustment, and can be used whether you are dealing with a whole date or a UTC time.

3. Make all machines use the same time zone.

4. Use Remoting to serialize the DataSet in binary form. This also has performance benefits. This KB article has an example.

5. If you have a chance to pre-process the XML before it is sent out, you can manually strip out the time zone offset out of the XML text. For example, a typical XML date and time looks like this: “2005-01-28T03:14:42.0000000-07:00”. You can use a Regex to remove the “-07:00”. You do not need to re-inject anything on the other end, as no adjustment is made if there is no time zone information. Do not try to replace the time zone offset with “Z” or “+00:00”. While technically a more correct representation, the existence of time zone information will cause the serializer to do an extra conversion to local.

This is the most difficult situation, because all of these work-arounds have problems. Option (1) involves bypassing the database type system. Option (2) has a reliability caveat explained below. I would actually recommend (4) or (5) for this technology.