Beware of DateTime serialization in Sync Framework n-tier synchronization


This post is prompted by a recent issue raised at the Sync Framework forum regarding DateTime synchronization. See http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/88637ae1-6f0d-4a51-9cd0-e7dff29c7d70 for a complete backgrounder.

If you have been serializing datasets containing DateTime columns, chances are you may not have noticed how the DateTime column values are adjusted when you pass/receive datasets to/from a remote service with differing time zones. The default behaviour is that DateTime column values are adjusted to the local time zone of the receiving end. See http://support.microsoft.com/default.aspx/kb/842545?p=1 for more details.

If you have configured Sync Framework for n-tier synchronization where you’re passing the change datasets to in between computers with different time zones, chances are you get this behaviour as well.

Some of the examples out there that’s commonly used as a starting point for doing syncs over n-tier are as follows:

How to: Configure N-Tier Synchronization

Programming Microsoft Synchronization Services for ADO.NET (Devices)

Walkthrough: Creating an Occasionally Connected Smart Device

Some of the fixes you can pursue are:

1. Use GETUTCDATE() instead of GETDATE() when setting the value of your DateTime column. However, there will be cases where you can’t change how the value is derived (e.g., a third party software like an ERP stores the value ).

2. Do some date manipulation in the SelectIncremental changes queries or the Insert and Update statements used by your adapters

3. Use the approach from http://support.microsoft.com/default.aspx/kb/842545?p=1 and do the date manipulation on either GetChanges/ChangesSelected on the source to store the UTC offset on the dataset ExtendedProperties and either ApplyChange/ApplyingChanges event on the receiving end to restore the date to the original value.

I hope this helps.

Advertisements

2 comments

  1. Madagaga · · Reply

    Fixed by applying offset when converting :
    public static string ConvertDateTimeToJson(DateTime date)
    {
    // Ticks returns the nanoseconds so to get milliseconds divide by 10,000
    return string.Format(USCultureInfo,
    FormatterConstants.JsonDateTimeFormat,
    ((date – FormatterConstants.JsonDateTimeStartTime).TotalMilliseconds – TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).TotalMilliseconds));
    }

  2. […] having all of the DateTime values in the cloud local looked like a reasonable option and this article provided some useful […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: