Help! Something adds 1 hour to my datetimes! :-S

[Pieter Coucke]

Hi,

For some reason, somewhere in my application 1 hour is added to my dates,
depending in which time zone the application is run...
Because I don't have a clue where this happens, I posted this to the three
relevant newsgroups (vb/ado, sql and xml): my aplogize for this.

I'm doing a Synchronisation between two SQL Servers (2000): All the Data is
read into a DataSet, and exported to an XML-file:
SQL Server -> DataSet (VB.NET 2003) -> XML -> DataSet -> SQL Server

When I run everything in the West Central Africa Timezone (GMT+1, no
summer-time): everything works fine. But I notice that all my dates in my
XML-file are written like this
"<DATE_DEB_APP>2006-02-01T00:00:00.0000000+01:00</DATE_DEB_APP> ": with the
"+01:00" at the end. But not that big of a problem, because the application
writes the date with 00:00 hours to the database.

I (well, my pc) is in the Brussels Timezone (GMT+1: but because of the
summer-hour we actuallt have GTM+2 now).
Everything goes fine also when I put the databse on my local SQL Server,
make the XML-file, read it again, and write it to the other database (also
local). All the dates are also presented with the "+01:00"-suffix. Except
one: a date that the application writes to the database when filling the
DataSet, and select it at the end to put it in the XML-file also: this one
gets the suffix "+02:00":
<SyncSend>2006-05-03T09:58:19.6070000+02:00</SyncSend>

So far so good: some not-really wanted things, but everything goes fine.

Problems start when I'm doing a synchronisation between an SQL server in
Central African TimeZone, and a local one in Brussels Timezone.
What hapens is that all my dates are having 1 hour added to them! So instead
of the "2006-02-01T00:00:00" they have as value "2006-02-01T01:00:00"!!

I somehow can understand why this happens; because 00:00h in Central Africa
is 01:00h in Brussels on this moment. But I don't want it to happen! I want
the exact time to be transfered!

Does anybody has a clue where this exactly happens? And how to prevent this?
Is there a way to say that it has to use the actual value? and not one that
calculates the time?

On this moment it's really nice to be able to use tha capbilities of the
DataSet with the XML-files and SqlDataAdapter with commandbuilder to do my
insert and updates. I won't really like to loose this :-S

Thanks a lot in advance!

Any help our hints would be really appreciated!

Pieter

 

[Cowboy \(Gregory A. Beamer\)]

Reading your post, it sounds like you want the Brussels events to use UTC
and the African events to not. If so, you will have to write in something
that detects where the event is occuring and adjust, if necessary.

The system, itself, will either use UTC (default) or local. If you wanted to
configure to one or the other, it is possible. But, configuring different
client locations to different methods is problematic and will require custom
coding.

Hope I understand the problem. Good luck!

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Stephen Ahn]

Pieter,

This is due to the standard way in which dotnet DateTimes are serialized and
deserialized.

To see what is happening, write a test app and, run code like this :

==
DateTime dt = // set to any DateTime you want to test
MessageBox.Show(dt.ToString("yyyy-MM-dd HH:mm:ss")); // X
string SerializedDate = XmlConvert.ToString(dt);
MessageBox.Show(SerializedDate); // Y
==
Close this test app, then change to another timezone on your PC, restart
your test app, then run code like this :
==
string SerializedDate = // set to exact string value shown when "Y" above
is run.
DateTime dt = XmlConvert.ToDateTime(SerializedDate);
MessageBox.Show(dt.ToString("yyyy-MM-dd HH:mm:ss")); // Z
==

You should see that the values shown by X and Z are different, as a result
of a DateTime being serialized in one timezone, and deserialized in another.

One possible solution might be to manually modify the serialized XML at the
deserialization end, just before deserialization takes place. You'd need to
tweak the timezone components of the serialized DateTime strings, so that
they would look like local DateTimes when they are deserialized. (This
applies mainly for dotnet 1.1 - there may be better ways in dotnet 2.0).

HTH,
Stephen

 

[Pieter Coucke]

Ok, Thanks for the advice Gregory and Stephen!