DateTimeOffset support not working

Compares and synchronizes SQL database content.

DateTimeOffset support not working

Postby dbouchard@tricomputer.com » Tue Apr 30, 2013 2:31 pm

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@tricomputer.com
 
Posts: 6
Joined: Thu Mar 11, 2010 5:28 pm

Postby chrishimsworth » Tue Jul 09, 2013 3:17 pm

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
 
Posts: 3
Joined: Tue Jun 19, 2012 9:51 am

Postby dbouchard@tricomputer.com » Tue Jul 09, 2013 3:45 pm

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@tricomputer.com
 
Posts: 6
Joined: Thu Mar 11, 2010 5:28 pm

datetimeoffset bug

Postby samvau » Tue Jul 23, 2013 9:47 am

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
 
Posts: 1
Joined: Tue Jul 23, 2013 9:40 am
Location: Finland

Upvote

Postby chrishimsworth » Tue Jul 23, 2013 10:06 am

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
 
Posts: 3
Joined: Tue Jun 19, 2012 9:51 am


Return to SQL Data Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest