I’ve found this example very interesting and making you understand DATE data type better.

SYSDATE function returns the current system date and time on your local database. But, if your NLS_DATE_FORMAT setting was set to DD-MON-RR, then it will return accordingly per specified format. Same is true about DATE data type. Therefore one may think that first two expressions are true because they both will show you the same output => for example shown below both columns SYSDATE and HIRE_DATE display the same value

Logically, the one may think that subtracting HIRE_DATE from SYSDATE may produce zero, or comparison will return TRUE. Respectively A and B are the right answers. But it is not. See below

A

B

Those two answers are actually wrong and the last two answers C and D are correct. Here is why. SYSDATE has a DATE data type, in other words, no difference between SYSDATE and DATE, they both have DAY, MONTH, YEAR, HOUR, MINUTE and SECOND stored in the database, therefore the comparison is happening between the real values stored in the database, and not between those that were shown after applying NLS_DATE_FORMAT. The default format for NLS_DATE_FORMAT is DD-MON-RR. But If I’ll change it to DD-MON-RR HH:MI:SS, then the answer will be obvious

Now, we see that time is different, therefore those two values are not equal and can’t produce zero when substructed, therefore. Now, it is clear why the answers C and D are correct ones.

C

D