none
Syncing row containing datetime column with date value 1753-01-01 00:00:00.000 on device RRS feed

  • Question

  • Hello.

    I have a sync setup with a 2008 sql db syncing through a WCF server to a Desktop client and a Device client.

    One of my tables has a row containing a datetime column with date value 1753-01-01 00:00:00.000.

    The Desktop clients syncs this row just fine, but on the device it's missing. 

    If I change it to a present date the device syncs the row just fine.

    I've testet it on the 6.0 professional emulator and on a 6.1 proffesional device.

    /Ole
    Monday, March 1, 2010 12:06 PM

Answers

All replies

  • hi Ole,

    have you tried checking on the ApplyChangesFailed event if there's anything being raised there that may have some information on why the row is not synching?

    Tuesday, March 2, 2010 2:42 AM
    Moderator
  • hi Ole,

    have you tried checking on the ApplyChangesFailed event if there's anything being raised there that may have some information on why the row is not synching?


    Hello June.

    Just tried it. 

    It gives me the following error message : "An overflow occurred while converting to datetime."

    /Ole
    Tuesday, March 2, 2010 9:08 AM
  • did you find any other error information in the innerException?

    Friday, March 5, 2010 3:55 PM
    Moderator
  • Hello Oled,

    I assume you are using SQL CE on device client.

    Just FYI, not all data types are compatible for sync because some data type support is not exactly same between SQL and SQL CE.

    Can you entering the data directly on CE and see what happens?

    Thanks,

    Patrick

    Friday, March 5, 2010 6:06 PM
  • Hello Oled,

    I assume you are using SQL CE on device client.

    Just FYI, not all data types are compatible for sync because some data type support is not exactly same between SQL and SQL CE.

    Can you entering the data directly on CE and see what happens?

    Thanks,

    Patrick

    Hello Patrick.

    The desktop client is also using SQL CE and it has no problem syncing the date.

    /Ole
    Monday, March 8, 2010 7:47 AM
  • did you find any other error information in the innerException?

    Hello June.

    The innerexception is null.


    But the stacktraces shows that the error is comming from the sqlcecommand. Maybe there is a difference between SQL CE on the desktop and on devices?

    /Ole
    Monday, March 8, 2010 8:25 AM
  • Please search for WCF datetime - this may be an issue with how datetime value is serialized over WCF, or a Nullable datetime issue.
    Monday, March 15, 2010 7:23 PM
    Moderator
  • Hi Ole,

    In addition, is the device timezone different than the source?


    JuneT
    Tuesday, March 16, 2010 2:38 AM
    Moderator
  • Hello.

    JuneT:

    The device timezone is the same as the server timezone.


    Sid:
    As far as I know I can't control how Sync Framework serialize datetimes over WCF?

    /Ole

    Wednesday, March 17, 2010 7:47 AM
  • Hello Old - "An overflow occurred while converting to datetime." appears in the ApplyChangesFailed event, what is the "source" value for this datetime field (it can be found through the event argument, SyncContext and DataSet property)?  Do you still see "1753-01-01 00:00:00.000"?

    Thanks
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 17, 2010 7:39 PM
    Answerer
  • Hello Leo

    e.Context.DataSet is null when the ApplyChangesFailed event is thrown.

    /Ole
    Thursday, March 18, 2010 11:08 AM
  • Hi Ole,

    How about the e.Conflict.ClientChange and e.Conflict.ServerChange? do you see the row there?

    Cheers,

    JuneT
    Thursday, March 18, 2010 1:10 PM
    Moderator
  • Hi June.

     

    e.Conflict.ClientChange is null.

     

    e.Conflict.ServerChange.Dataset is null. 

     

    There is a column and a row collection under serverchange, but I can't see the values in the row collection. The column collection has the correct columns in it.

     

     

    /Ole

    Friday, March 19, 2010 7:46 AM
  • try this, replacing "YourDateColumnName" with the name of your date column

    e.Conflict.ServerChange.Rows[0]["YourDateColumnName"]

    Friday, March 19, 2010 8:58 AM
    Moderator
  • hi ole,

    is this the date you're getting when you sync 1753-01-01 00:00:00.000?

     

    Friday, March 19, 2010 2:57 PM
    Moderator
  • Hello June.

     

    Yes it is.

     

    Is it a timezone problem?

     

    /Ole

    Monday, March 22, 2010 7:03 AM
  • maybe...

    your 1753-01-01 looks like it went back to 1752-12-31 (i cant tell exactly cause the image you provided doesnt have the year).

    can you look at the other dates and see if they adjusted when reaching the client?

    JuneT

    Monday, March 22, 2010 9:52 AM
    Moderator
  • Hello June.

    It is a timezone problem.

    Our server timezone is GMT +1.

     

    A couple of test results with different timezones on a PDA.

    Date in sql server: 2010-02-01 00:00:00.000


    Regional setting English
    Timezone -8
    1/31/2010 3:00:00 PM

    Regional setting Danish
    Timezone +1
    2/1/2010 12:00:00 AM

    Date in sql server: 1753-01-01 00:00:00.000

    Regional setting English
    Timezone -8
    Exception - row is not synced.

    Regional setting Danish
    Timezone +1

    1/1/1753 12:00:00 AM

     

    We will have PDA's all over the world connecting to this server. Any suggestions to a workaround on this?

     

    /Ole

    Wednesday, March 24, 2010 1:35 PM
  • In this case, using UTC might be a solution (calling GETUTCDATE() instead of GETDATE()).

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, March 24, 2010 5:07 PM
    Answerer
  • Problem is that we don't have any control over these dates, they are coming from an ERP system.

    /Ole
    Wednesday, March 24, 2010 6:23 PM
  • You can convert it to UTC on the client - since you know what timezone the date is for, then convert back into local time.
    Wednesday, March 24, 2010 6:45 PM
    Moderator
  • try the approach here: http://support.microsoft.com/kb/842545

    you can do the conversion on the ApplyingChanges event of the client provider.

    • Proposed as answer by P H T Friday, June 3, 2011 10:14 PM
    Wednesday, March 24, 2010 8:51 PM
    Moderator