Sunday, February 19, 2012

Either SQL or VB is automatically applying time offsets on my data

I have a windows app which is used in australia and uses web services to communicate with the database located in new zealand. I have a problem where dates that are returned from SQL are automatically modified to suit the timezone and i dont know how to turn it off.

For example, i insert the value '10/Jan/2006 20:00' into the database from a PC located in australia. When that data is selected back out, a 4 hour time offset is automatically applied to it and the time is returned as '10/Jan/2006 16:00'.

My current workaround is to modify my queries to say;

"Select convert(varchar, tb_date) tb_date from tablename"

Which returns the date as a string rather than a datetime which means the time offset is not applied.

This is a really poor solution and i'd be really keen to find out a better way...
Thanks

This is not problem with VB or SQL. This is because of the way DateTimes are serialized in XML when sent over Web Services. For information read this excellent article on working with DateTimes in TimeZone involved applications.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/datetimecode.asp

No comments:

Post a Comment