locked
SQL Sync - Date Correct / Time is wrong RRS feed

  • Question

  • I set up my first Sync services app syncing my MDF and my SDF.  All works fine, but the time on the last edit date & creatin date is incorrect.

     

    Where does this pull from?  My system date & time are correct.

     

    Thanks!

    • Moved by Max Wang_1983 Friday, April 22, 2011 7:51 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, April 29, 2008 7:39 PM

Answers

  •  

    I guess we know the reason now. where are you located ( or the time zone you system was set up to ) ? if you have 6 hour difference from the UTC time, then this is the reason.

     

    you can try the following it should give you the same.

    select GetDate()

    select GetUTCDate()

     

    So, please make sure your selectAnchorCommand use GetUTCDate() also since you triggers used this time for tracking.

     

    thanks

    Yunwen

    Sunday, May 4, 2008 4:47 PM
    Moderator
  • Fixed it....for now at least.

     

    I changed GETUTCDATE() to GETDATE() in my triggers only.

     

    Works fine.

     

    Thanks for the responses.

     

    V

     

    Sunday, May 4, 2008 5:10 PM
  •  

    as long as your tracking triggers(defaults) and the selectNewAnchorCommand use the same function, you should be fine. the GETUTCTIME () is actually the recommended one.

     

    thanks

    yunwen

    Sunday, May 4, 2008 5:10 PM
    Moderator

All replies

  • I still have not found an answer to this.  I tested another comptuter & it is off an hour...all of my system date/times are showing correct.

     

    Is there a setting in SQL server or something??

     

    Wednesday, April 30, 2008 6:15 PM
  • would you add more details here ?

     

    what are the default, triggers look like on you table ?

     

    thanks

    Yunwen

     

    Saturday, May 3, 2008 2:16 AM
    Moderator
  • Thanks for responding:

     

    Here is the default insert trigger:

     

    ALTER TRIGGER [weight_train_log_InsertTrigger]

    ON [weight_train_log]

    AFTER INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    UPDATE [weight_train_log]

    SET [CreationDate] = GETUTCDATE()

    FROM inserted

    WHERE inserted.[ID] = [weight_train_log].[ID]

    END;

     

     

    Default Update trigger:

     

    ALTER TRIGGER [weight_train_log_UpdateTrigger]

    ON [weight_train_log]

    AFTER UPDATE

    AS

    BEGIN

    SET NOCOUNT ON

    UPDATE [weight_train_log]

    SET [LastEditDate] = GETUTCDATE()

    FROM inserted

    WHERE inserted.[ID] = [weight_train_log].[ID]

    END;

     

    Default Data or Binding for Both fields above:  (getutcdate())

     

    All timezones are set correctly.

     

    Thanks!

    Saturday, May 3, 2008 11:57 AM
  •  

    the triggers look correct.

     

    By "the time on the last edit date & creatin date is incorrect.", what are the actual value and what are the values you expected to be ? i.e. why do you think the datetime for tracking columns ( creationDate and LastEditDate ) are incorrect ?

     

    thanks

    yunwen

    Sunday, May 4, 2008 12:16 AM
    Moderator
  • It is always 6 hrs ahead (to the second).

     

    If I put somthing in the DB on 05/04/2008 1:15:25 AM, then it would post in the creation/lastedit as 05/04/2008 7:15:25 AM

     

    Would getdate work better here s getutcdate()?  Doesn't get date look at the CPU system date or am I confused on that?

    Sunday, May 4, 2008 12:31 PM
  •  

    I guess we know the reason now. where are you located ( or the time zone you system was set up to ) ? if you have 6 hour difference from the UTC time, then this is the reason.

     

    you can try the following it should give you the same.

    select GetDate()

    select GetUTCDate()

     

    So, please make sure your selectAnchorCommand use GetUTCDate() also since you triggers used this time for tracking.

     

    thanks

    Yunwen

    Sunday, May 4, 2008 4:47 PM
    Moderator
  • I am using GETUTCDATE() (default by sync services) for triggers & the default value in the MDF database.

     

    SHould I use GETDATE() instead?

     

    The system and software & everything was set up US Central Time.

     

    Thanks,

     

    V

     

    Sunday, May 4, 2008 5:03 PM
  • Fixed it....for now at least.

     

    I changed GETUTCDATE() to GETDATE() in my triggers only.

     

    Works fine.

     

    Thanks for the responses.

     

    V

     

    Sunday, May 4, 2008 5:10 PM
  •  

    as long as your tracking triggers(defaults) and the selectNewAnchorCommand use the same function, you should be fine. the GETUTCTIME () is actually the recommended one.

     

    thanks

    yunwen

    Sunday, May 4, 2008 5:10 PM
    Moderator