How to DataSet.Fill with DateTime values defaulting to DateTimeKind.Utc?

Question

I have an application which would read data from SQL and send it via WCF to a client app, in a manner similar to this:

SqlDataAdapter da = new SqlDataAdapter( cmd )
DataSet ds = new DataSet();
da.Fill( ds );
return ds;

All the date/times are stored in the database as UTC. What I noticed is that if clock on computer running the application is skewed, the date/times received by clients will be skewed as well. It seems that in case when DateTime type is of unspecified kind, WCF will represent it as local time internally, and send as such, so any time difference between the application and the client will cause date/time to shift.

I could certainly go through datasets as they are retrieved and fix date/time fields, but would anybody here think of some better way to fill the dataset, so that every DateTime field would automatically be DateTimeKind.Utc on da.Fill()?

Answer 1

If you're using SQL Server 2008, then the "proper" solution is to use the SQL datetimeoffset data type instead of SQL's datetime. datetimeoffset is a timezone-aware date/time type new to SQL 2008, and will be translated in ADO.NET into the CLR System.DateTimeOffset type which is always relative to UTC. Here's a blog post going into more details about this. The first few search results on MSDN for DateTimeOffset provide additional background info.

If you can't change your SQL schema, ADO.NET has a property custom-made for this situation: DataColumn.DateTimeMode, which controls whether local timezone is added when the dataset is serialized (DateTimeMode=DataSetDateTime.UnspecifiedLocal, which is the default) or whether no timezone info is added on serialization (DateTimeMode=DataSetDateTime.Unspecified). You want the latter.

If my reading of the MSDN docs is correct, you should be able to set DateTimeMode=DataSetDateTime.Unspecified for the DataColumn in question after the DataSet is filled. This should serialize the column without a timezone.

If you want to be really proper (or in case the approach above doesn't work), you can create the DataTable ahead of time and set that column's DateTimeMode=DataSetDateTime.Utc before Fill-ing it with rows. Then you're assured of sending the date as UTC.