CODEWITHSUNDEEP
oracle-database
InTheWorldOfCodingApplications
InTheWorldOfCodingApplications

Reputation: 2616

Date expression in Oracle

How will the following expression be evaluated to in Oracle?

WHERE nvl(MODIFIEDDATE, TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS'))
  > TO_DATE('', 'YYYY/MM/DD HH24:MI:SS')

Can anyone explain what this would do? I am not Oracle person.

Upvotes: 0

Views: 160

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Let's walk through this in detail:

NVL(MODIFIEDDATE, TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS'))

This will return MODIFIEDDATE if it is not NULL. If MODIFIEDDATE is NULL then it will return a DATE value of 2030/01/01 00:00:00 (the time component will default to midnight because it's not specified in the time literal of '2030/01/01'). So this will always return a non-NULL DATE value.

TO_DATE('', 'YYYY/MM/DD HH24:MI:SS')

This will always return NULL. A zero-length string, in Oracle, means NULL. This is by design (it goes back to the very earliest days of Oracle), and is IMO one of the worst design decisions in the Oracle product. But it's what it is - a zero-length string in Oracle is NULL, always and forever. And NULLs are poison, so TO_DATE(NULL, 'YYYY/MM/DD HH24:MI:SS') will return NULL.

So now we've got

SOME_VALID_DATE_VALUE > NULL

This will always return NULL, because any comparison with NULL returns NULL (remember - NULLs are poison). So the WHERE clause becomes

WHERE NULL...

And thus the query which uses this WHERE clause will return no rows.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You are comparing a date with NULL.

This:

nvl(MODIFIEDDATE, TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS'))

Means, whenever MODIFIEDDATE is NULL, use the date value instead. 

SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> SELECT TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS') LHS, TO_DATE('', 'YYYY/MM/DD HH24:MI:SS') RHS FROM dual;

LHS                 RHS
------------------- -------------------
2030/01/01 00:00:00

SQL>

So, basically, comparing anything with NULL will result is...."unknown". Therefore, your query would return nothing.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191245

The first half of the condition:

nvl(MODIFIEDDATE, TO_DATE('2030/01/01', 'YYYY/MM/DD HH24:MI:SS'))

... means that if the MODIFIEDDATE column is null, a magic date of 2030-01-01 will be used instead.

The second half of the condition:

TO_DATE('', 'YYYY/MM/DD HH24:MI:SS')

... will always evaluate to null. Null cannot be compared to any other value including itself, except with the is [not] null operator.

The result of this condition will always be 'unknown', so no rows will be returned.

It will exclude all rows, not just those that have a null date; the nvl() means that any null values are given a dummy value anyway. If you only wanted to exclude those with a null date you could specify WHERE MODIFIEDDATE IS NOT NULL, but it isn't really clear that's what is intended by that code.

Upvotes: 1

Related Questions