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.
