Compare Synchronization Wizard creates incorrect syntax for datetimeoffset
説明
In Oracle, when you have a table with a column with the datatype "timestamp with local timezone", and in SQL Server, a table with a column with "datetimeoffset", then compare the data in both tables, ensure it is out of sync (null values in SQL Server).
Then run the synchronization wizard - and receive an error with “Conversion failed when converting date and/or time from character string”.
When looking at the syntax, you can see the following statement created by TDP: UPDATE [dbo].[datetest] SET [dtoffset] = '2015-09-18T12:44:02.50343 +02:00' WHERE [sp1] = 1
The correct syntax is: UPDATE [dbo].[datetest] SET [dtoffset] = '2015-09-18 12:44:02.50343 +02:00' WHERE [sp1] = 1
The difference is the T between the date and time portions - SQL Server doesn't recognise it. This syntax would be correct for columns with the datatype datetime2, but not for datetimeoffset.
Steps to reproduce: 1) In Oracle: create table datetest (sp1 number, tstamp timestamp(6) with local time zone); insert into datetest values (1, systimestamp); commit; 2) In SQL Server: create table datetest (sp1 int, tstamp datetimeoffset(7)); insert into datetest (sp1) values (1); 3) In TDP, compare both datetest tables. You will get one different row. Then run Synchronization Wizard. If you have the sync script run immediately, it will fail. If you open the sync script in Toad Editor and omit the T between date and time (see above), it will succeed.
対策
WORKAROUND: Manually remove the 'T' character in the statement.
STATUS: Fixed in Toad Data Point v3.8, available to download here
