locked
Different DateTime formats are not considered RRS feed

  • Question

  • Hello,

    I just run into trouble, because I am using a german SQL 2005 server for a project.
    The germin server stores DateTime in the german time format (03.07.2009 11:53:05).

    But my win ce client is based on an english version,
    so the local client database handles the english time format (2009-07-03 11:53:05).


    The data is transfered into the right format with insert and updates after initial sync.

    But I am running into problems with the delete-queres:

    E.g.

    SELECT 
    [GERAET_UID], [GERAET_ID],[DELETE_DATE] 
    FROM dbo.AE_GERAET_T_Tombstone WHERE 
    [DELETE_DATE] > '2009-07-03 11:44:26:147' AND
    [DELETE_DATE] <= '2009-07-03 11:59:13:223'
    
    Does not give any results, although it should,
    because SQL Server cannot transfer the DateTime-Strings into the proper format.


    Do you know this problem?

    What would be the best way to solve this problem?


    Regards,

    Martin

    • Moved by Hengzhe Li Friday, April 22, 2011 2:32 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, July 3, 2009 10:25 AM

Answers

  • Can you try GETUTCDATETIME() Instead of GETDATETIME() to fillin the datatime column?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 22, 2009 6:20 PM
    Answerer

All replies

  • Well I think it is another problem.

    The Delete-Queries are defined as DateTime, but are delivered by the Sync-Framework as Strings.


    Original Command:

           
         this.SelectIncrementalDeletesCommand = new System.Data.SqlClient.SqlCommand();
    // ID => UID
    this.SelectIncrementalDeletesCommand.CommandText = "SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC WHERE (@sync_initiali" +
    "zed = 1 AND [DELETE_DATE] >@sync_last_received_anchor AND [DELETE_DATE] <= @sync_new_received_anchor)";
    this.SelectIncrementalDeletesCommand.CommandType = System.Data.CommandType.Text;
    this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_initialized", System.Data.SqlDbType.Bit));
    this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
    this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));



    Generates the following command:

    exec sp_executesql N'SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC
    WHERE (@sync_initialized = 1 AND
    [DELETE_DATE] > @sync_last_received_anchor AND
    [DELETE_DATE] <= @sync_new_received_anchor)',
    N'@sync_initialized bit,@sync_last_received_anchor datetime,@sync_new_received_anchor datetime',
    @sync_initialized=1,@sync_last_received_anchor='2009-07-03 13:01:39:233',@sync_new_received_anchor='2009-07-03 13:03:10:783'


    The DateTimes come as string, although I´ve defined them as DateTime. So the SQL Server does not give any results back.


    This would work:

    SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC
    WHERE DELETE_DATE > CONVERT(DATETIME, '2009-07-03 13:01:39:233',21) AND
    [DELETE_DATE] <= CONVERT(DATETIME, '2009-07-03 13:03:10:783', 21)

    But when I change the Sync-Queries:

                this.SelectIncrementalDeletesCommand.CommandText = "SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC WHERE (@sync_initiali" +
                    "zed = 1 AND [DELETE_DATE] > CONVERT(DATETIME, @sync_last_received_anchor, 21) AND [DELETE_DATE] <= CONVERT(DATETIME, @syn" +
                    "c_new_received_anchor, 21))";
                this.SelectIncrementalDeletesCommand.CommandType = System.Data.CommandType.Text;
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_initialized", System.Data.SqlDbType.Bit));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));

    The following output is received by the SQL-Server:

    exec sp_executesql N'SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC
    WHERE (@sync_initialized = 1 AND
    [DELETE_DATE] > CONVERT(DATETIME, @sync_last_received_anchor, 21) AND
    [DELETE_DATE] <= CONVERT(DATETIME, @sync_new_received_anchor, 21))',
    N'@sync_initialized bit,@sync_last_received_anchor datetime,@sync_new_received_anchor datetime',
    @sync_initialized=1,@sync_last_received_anchor='2009-07-03 13:01:39:233',@sync_new_received_anchor='2009-07-03 13:03:10:783'


    But this does not work again.


    Am I the only one who is  having this problem?



    Regards,

    Martin
    Friday, July 3, 2009 11:23 AM
  • Well I´ve found a solution which "seems" to work:

                this.SelectIncrementalDeletesCommand.CommandText = "SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC WHERE (@sync_initialized = 1 AND [DELETE_DATE] > CAST(CONVERT(char(23), @sync_last_received_anchor, 21) AS datetime) AND [DELETE_DATE] <= CAST(CONVERT(char(23), @sync_new_received_anchor, 21) AS datetime))";
                this.SelectIncrementalDeletesCommand.CommandType = System.Data.CommandType.Text;
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_initialized", System.Data.SqlDbType.Bit));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));
                this.SelectIncrementalDeletesCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));


    This generates the following command:

    exec sp_executesql N'SELECT [GERAET_UID], [GERAET_ID],[DELETE_DATE] FROM dbo.AE_GERAET_T_SYNC WHERE (@sync_initialized = 1 AND [DELETE_DATE] > CAST(CONVERT(char(23), @sync_last_received_anchor, 21) AS datetime) AND [DELETE_DATE] <= CAST(CONVERT(char(23), @sync_new_received_anchor, 21) AS datetime))',N'@sync_initialized bit,@sync_last_received_anchor datetime,@sync_new_received_anchor datetime',@sync_initialized=1,@sync_last_received_anchor='2009-07-03 13:03:10:783',@sync_new_received_anchor='2009-07-03 13:53:30:170'


    This "seems" to work, because I get two rows back when I run this command in Management Studio.

    BUT.... these two rows are NOT deleted on client side.


    Does anybody has an idea where my problem is?




    Regards,

    Martin
    Friday, July 3, 2009 12:23 PM
  • this is an interesting thread. I recalled long time back there is an similar issues discussed on this.

    a few things need to mention here:

    1. cross locale sync is not supported. the issues you described here ( datetime format differences from one side to the other side) is one of the reasons.
    2. for cases as such, timestamp or other tracking type is recommented. actauly timestamp is always recommented over datetime
    3. as Macap already found out, explictly convert the datetime to a particular format would help here, you can also do this at the OS datetime format level for choose the right one. However, as stated in #2, timestamp is recommented.

    Hope this helps.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 6, 2009 6:50 AM
    Moderator
  • Hello Yunwen,

    thanks for your answers. I will try it. If timestamps are recommended so much, why do so many tutorials, especially the beginners tutorials use datetime instead of timestamp?
    In my opinion it does not make any sense, because the using of DateTime instead of Timestap does not make a beginners tutorial more easier or something like that.

    Also I´ve created my first sync table commands via the LocalDataBase-SyncDesigner of Visual Studio. So it seems to me, that this designer also has a bug when using DateTime columns.


    Regards,

    Martin
    Monday, July 6, 2009 7:31 AM
  • I just fixed my bug. It´s very strange, but my bug was not about casting the datetime object or not. The problem was, that I used UTC-Date for inserts and local Date for the normal operations.

    But all in all it seems to be a bit weird to me.


    Why does it work without casting the strings to datetime?

    I runned the SQL Server Profiler and snapped the SQL-Queries generated by the Sync-Framework
    When I run them in Management Studio, the do not work. Which means that I do not get back the deleted rows out of the Tombstone-Tables between my last-recieved-anchor and the
    new recieved anchor.

    But for the Sync-Framework it seems to work. My rows are deleted now on the clients.


    Monday, July 6, 2009 12:10 PM
  • Hi,

    You may use datetime-agnostic anchor colums by using timestamp data type instead of datetime data type. For example, consider:


    ALTER TABLE SyncSamplesDb.Sales.Customer 
    	ADD UpdateTimestamp timestamp
    ALTER TABLE SyncSamplesDb.Sales.Customer 
    	ADD InsertTimestamp binary(8) DEFAULT @@DBTS + 1
    GO
    


    Instead of:

    ALTER TABLE SyncSamplesDb.Sales.Customer 
    	ADD UpdateTimestamp DateTime
    ALTER TABLE SyncSamplesDb.Sales.Customer 
    	ADD InsertTimestamp DateTime DEFAULT GetUTCDate()
    GO
    

    Cheers,




    José Miguel Torres
    Monday, July 6, 2009 2:33 PM
  • Hello Yunwan,

    as you mention, timestamp is always recommented,
    But timestamp values are automatically generated in local server and the values are unque within a specific database, we cannot apply a change to the timestamp value created at one server to the timestamp column at another server.

    If this is the cases, why Sync Framework are depend on timestamp and not date time?




    I have read some article about timestamp.

    I have did some experiment and it is true when I CreateTimestamp and Updatetimestamp column is timestamp datatype is work incorrect.


    Can you explain more actaul real life enviroment which direction should we really go for?

    Thanks




    Friday, July 10, 2009 3:24 AM
  • Good question Matrixgoth!

    I asked this myself also before.
    Would be happy for any interesting answer :-)


    Regards,
    Martin
    Friday, July 10, 2009 10:09 AM
  • Can you try GETUTCDATETIME() Instead of GETDATETIME() to fillin the datatime column?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 22, 2009 6:20 PM
    Answerer