Comparing two dates in Oracle after using TO_DATE function

I have the following Oracle Query that is converting todays date and the date field from the table into the same format. However, when trying to compare the two they aren’t coming up as equal.

CAST(dstamp As Date), TO_DATE(CURRENT_DATE,'dd-MON-YY HH24.MI.SS','NLS_DATE_LANGUAGE = American')

​x
 
CAST(dstamp As Date), TO_DATE(CURRENT_DATE,'dd-MON-YY HH24.MI.SS','NLS_DATE_LANGUAGE = American')​

The cast is used on the field in my table, both these return.

However, when adding the following where statement no rows are returned. I can’t work out why these wouldn’t be classed as equal?

WHERE CAST(dstamp As Date) = TO_DATE(CURRENT_DATE,'dd-MON-YY HH24.MI.SS','NLS_DATE_LANGUAGE = American');

 
WHERE CAST(dstamp As Date) = TO_DATE(CURRENT_DATE,'dd-MON-YY HH24.MI.SS','NLS_DATE_LANGUAGE = American');​

Any help appreciated.

Answer

The date is ALREADY a date. You don’t need to convert it. You may need to remove the time component. Does this do what you want?

WHERE TRUNC(dstamp) = TRUNC(sysdate)

 
WHERE TRUNC(dstamp) = TRUNC(sysdate)​

Advertisement

Answer