How do you prevent time zone translation on a SqlConnection?

Question

Hi, I've got a database here that runs entirely on GMT. The client machines, however, may run on many different time zones (including BST). When you pull data back using SqlConnection, it will translate the datetime value so, for instance

19 August 2008

becomes

18 August 2008 23:00:00.

My question is, is there a way to specify to the connection that you do not wish this translation to take place?

Answer 1

Only keep UTC values in the database. Have your business objects always translate to/from UTC/local time when storing/retrieving from the database, so that users can view and enter local time values. You can do this by implementing the translation in the getter/setter methods of the BO so that the private variable is in UTC and thus when it is stored back to the database it is already in the form you need.

Answer 2

How are you accessing the data?

I had the same problem when passing DataSets / DataTables from a webservice.

I got round it by setting the DataColumn.DateTimeMode property in the DataTable

returnedDataTable.Columns("ColumnName").DateTimeMode = DataSetDateTime.Unspecified

It was just fine after that.

Don't know if this will help.