Date comparison problem

CloverDX Platform
1

Im using Comparator in order to compare 2 tables, each one with the same name and number of fields. One of those fields is a Oracle date but when comparison gets done, the Comparator detects that 01-JAN-1970 14:00:00 and 01-JAN-1970 02:00:00 are the same. In the metadata the fields are declared as follows :

$lt;Field name="CHECKINTIME" type="date" nullable="yes" delimiter="," /$gt;

How date comparison gets done? and how can I solve the problem. Do I need to use a locale or format in metadata?

Any help will be highly appreciated

2

Hello,
how do you compare the dates? They are really different? Or maybe they are only differently formatted :
H Hour in day (0-23) Number 0
k Hour in day (1-24) Number 24
K Hour in am/pm (0-11) Number 0
h Hour in am/pm (1-12) Number 12

3

Thanks for your help,

Let me tell you that actually Im not comparing the dates manually, im using a DATA_INTERSECTION component with 2 DB_INPUT_TABLE. Each DB_INPUT_TABLE uses the same query to 2 different databases, but when comparison gets done by DATA_INTERSECTION it detects that both dates (the ones i mentioned in the first post) are equal. In the metadata Im not specifying any formating nor locale, the dates are processed as it was loaded from database.

In my first post I used the word Comparator but my mistake, it must say DATA_INTERSECTION

Please help me.

Best Regards

4

I’ve tried to reproduce your problem but in my case dates haven’t been regarded as equal. I think that your dates has, in fact, the same value, but you only see them as different.
I put your dates to oracle and postgre databases, and then read and compared them by Intersection component. ‘01-JAN-1970 14:00:00’ was joined with ‘01-JAN-1970 14:00:00’ only and ‘01-JAN-1970 02:00:00’ was joined with ‘01-JAN-1970 02:00:00’ only.

5

Hi,

I allready solved the problem, I just put format attribute in metadata. Seems like if field metadata doesnot contains format it is treated as a date ignoring time part. If the field in oracle database is timestamp it works but if its date clover just compare date part no matter that oracle date also supports datetime values.

Thank you very much for your help.