I am trying to compare the date fields between two of my tables T1 and T2.
I have framed a query similar to below and doing the comparison:
Query:
SELECT distinct t1.id t1_id, t2.id t2_id, t1.source t1_source, t2.source t2_source, to_date(t1.t1_date,'MM-DD-YYYY') t1_date, to_char(t2.t2_date,'DD-MON-YY') t2_date
FROM t1
JOIN t2 ON t1.id=t2.id
AND t1.source = t2.source
and to_date(t1.t1_date,'MM-DD-YYYY') <> to_char(t2.t2_date,'DD-MON-YY');Though the T1_DATE and T2_DATE are one and the same, the results are popping up that they are not equal.
------------------------------------------------------------------------
T1_ID | T2_ID | T2_SOURCE | T2_SOURCE | T1_DATE | T2_DATE
------------------------------------------------------------------------
123 | 123 | SOU | SOU | 17-FEB-47 | 17-FEB-47
234 | 234 | SOU | SOU | 01-JAN-49 | 01-JAN-49
------------------------------------------------------------------------- I did copy pasted
T1_DATEandT2_DATEto notepad and confirmed that they don't have any leading or trailing spaces. - I verified from the table that they don't have any leading or trailing spaces.
- The date condition I added for comparison too doesn't have any spaces.
I now updated my query to add trim to my condition:
Updated query:
SELECT distinct t1.id t1_id, t2.id t2_id, t1.source t1_source, t2.source t2_source, to_date(t1.t1_date,'MM-DD-YYYY') t1_date, to_char(t2.t2_date,'DD-MON-YY') t2_date
FROM t1
JOIN t2 ON t1.id=t2.id
AND t1.source = t2.source
and trim(to_date(t1.t1_date,'MM-DD-YYYY')) <> trim(to_char(t2.t2_date,'DD-MON-YY'));And now it worked fine. What is happening in the backend?
Please advice.
1条答案
按热度按时间sr4lhrrt1#
When you compare with:
you are comparing a date on the left-hand side with a string on the right-hand side. That is forcing Oracle to do an implicit conversion so that they are both the same data type, to allow the values to be compared.
The data comparison rules say:
So the string you just generated is converted back to a date, so you're really doing:
... which used your NLS settings, and your NLS_DATE_FORMAT appears to be DD-MON-YY too. The string is going to be '17-FEB-47', and when you convert that back to a date with the two-digit YY format mask that uses the current century, so it comes out as 2047, not 1947. So you end up effectively doing:
... which is true, as the dates are in different centuries, hence your rows are displayed.
But when you do:
you are now comparing two strings. The left-hand side is converted to a date and now that is implicitly converted back to a string, again using your NLS setting, and the right-hand side isn't modified. So you end up effectively doing:
... which is false, so the rows are not shown.
So, either leave the right-hand side as its original date value:
or if that has a time part you want to ignore then truncate it, but leave it as a date:
fiddle