How can we help you today? How can we help you today?

DateTimeOffset support not working

I have 2 databases with several tables that contain datetimeoffset fields. I have emptied one of the databases so that sdc10 will basically be doing a move from one db to the other and the script created by sdc10 fails on a datetime conversion error. I find it hard to believe that I'm the first person to run into this!

Here is a sample import row below. The fields CreatedDateTime and ModifiedDateTime are both datetimeoffset(7) fields.

INSERT INTO [dbo].[BuildLabels] ([RowID], [ParentRowID], [BuildLabel], [Comments], [SystemLabel], [OwnerID], [CreatedDateTime], [CreatedBy], [ModifiedDateTime], [ModifiedBy], [Active], [rowguid], [LabelState]) VALUES (1, NULL, N'root', N'main root label', 1, N'administrator', '2012-11-12 16:59:01.1745892 +05:30', N'donaldb', '2012-11-12 16:59:01.1745892 +05:30', N'donaldb', 1, '5f2cc758-d832-e211-ad6c-d067e5e5d59c', NULL)

The exact error in a messagebox titled "SQL Data Compare" is "Error occurred execution synchronization. Conversion failed when converting date and/or time from character string."

Thanks,
Don
dbouchard
0

Comments

4 comments

Sort by
  • chrishimsworth
    I too have 2 databases that use datetimeoffset and get the following message when I try to copy data from one to the other:

    [241] Conversion failed when converting date and/or time from character string.

    Here is a sample row:
    INSERT INTO [dbo].[Information] ([DepartureLocal], [DepartureUTC]) VALUES ('2013-06-20 19:20:00.0000000 +08:00', '2013-06-20 11:20:00.0000000 +00:00')
    chrishimsworth
    0
  • dbouchard
    Just FYI, the issue is that the RG tool will create inproper time entries, like the following: 2013-07-13 14:60:00.1234567 -04:00 instead of what should be which is:
    2013-07-13 15:00:00.1234567 -04:00. If you search in your script, you will see some :60: entries...

    Extremely frustrating...
    dbouchard
    0
  • samvau
    I found another variant of datetimeoffset bug.
    In script: '2011-10--01 01:26:51 +02:00'
    In source db: '2011-09-29 01:26:51 +02:00'

    Just hope someone is working on this.
    samvau
    0
  • chrishimsworth
    I logged a support call with RedGate and the issue is SDC-1593.
    Please upvote this on uservoice, to get the dev team to start work on it - http://redgate.uservoice.com/forums/147879-sql-data-compare-feature-suggestions/suggestions/4218369-datetimeoffset.
    chrishimsworth
    0

Add comment

Please sign in to leave a comment.