While Comparing Sqlserver:Datetime2 with Oracle:Timestamp, it displays Diff in Data Diff viewer in Toad Data Point 4.0

Toad Data Point & Toad Intelligence Central
1

Please help me out for this issue to compare Sqlserver:Datetime2 with Oracle:Timestamp in Toad Data Point 4.0.

I ran the sql query in Editor1(SQL Server DB) and Editor2(Oracle DB)
SELECT ANSWER_ID, ANSWER_CAPTION, EFFECTIVE_DATETIME FROM DIM_ANSWER

Data in Data set in Editor1 and Editor2 displays same format for EFFECTIVE_DATETIME as yyyy-MM-dd HH:mm:ss.ffffff.
However, when I tried to compare these two data sets, there are always show different between 2 EFFECTIVE_DATETIME fields.

Status ANSWER_ID ANSWER_CAPTION ANSWER_CAPTION EFFECTIVE_DATETIME EFFECTIVE_DATETIME
Different 0 UNKNOWN NKNOWN 10/13/2014 9:40:04.227 PM 10/13/2014 9:40:04 PM

Setting as follows.
In Tools->Options->Environment->Grid
Data Type Formatting:
DateTime Tab : ISO pattern(yyyy-MM-dd HH.mm.ss.ffffff)
Custom Tab : SqlServer:DATETIME2 DateTime yyyy-MM-dd HH.mm.ss.ffffff

If I did it in Toad Data Point 3.6, there are no different at all.

How can this issue be fixed?

Regards,

Jerry

2

I think the problem is your data type. try it where the data type is timestamp in both places. If the data type is Date one of the two systems does not store miliseconds (I forget which one but I know I ran into this before). Hope that helps.

3

Jerry,

Do you use 32bit or 64bit Oracle client? What versionof SqlServer are you using? If you can provide table DDL for both tables and some sample data, we will investigate the issue at our side.

Thanks!

4

Kiki,

I appreciate on your help . I tried to compare Oracle client 11g 64bit in Windows 7, TIMESTAMP (6) with SqlServer 2012, DATETIME2(6).

It works perfectly in TDP 3.6.

DDL defines as follows.

ANSWER_ID Integer
ANSWER_CAPTION varchar2(1000)
ORDER_ID Integer
EFFECTIVE_DATETIME Timestamp(6)

VS

ANSWER_ID int
ANSWER_CAPTION varchar(1000)
ORDER_ID int
EFFECTIVE_DATETIME Datetime2(6)

Thank you.

5

Jerry,

I created QAT-9411 to investigate the issue. In the mean time, please try using the following functions to convert the vaule to string then compare the result sets.

Oracle – TO_CHAR (EFFECTIVE_DATETIME , ‘YYYY-MM-DD HH:mm:ss.ff6’)

SQL Server – FORMAT (EFFECTIVE_DATETIME , ‘yyyy-MM-dd HH:mm:ss.ffffff’)

6

Hi Kiki,

I tried using the following functions. It works fine.

Oracle – TO_CHAR (EFFECTIVE_DATETIME , ‘YYYY-MM-DD HH24:mi:ss.ff6’)

SQL Server – FORMAT (EFFECTIVE_DATETIME , ‘yyyy-MM-dd HH:mm:ss.ffffff’)

Thank you for support.

Regards,

Jerry