Database Administration (MOSC)

MOSC Banner

Using NVL function on Date column

edited May 3, 2012 3:34AM in Database Administration (MOSC) 3 commentsAnswered
Hello,

Please explain the behavior of Oracle when treating empty string and null for DATE datatype.

For example:

create table delete_me1(x number, b date);

insert into delete_me1 values (1, to_timestamp('11/11/2011 22:29:59.999','DD/MM/YYYY HH24:MI:SS.FF3'));
insert into delete_me1 values(1,to_date('11/11/2011 22:29:59','DD/MM/YYYY HH24:MI:SS'));
insert into delete_me1 values(1,to_date('11/11/2011','DD/MM/YYYY'));

select count(1)
from delete_me1 a
where nvl('',b)=b;
COUNT(1)
--------
1

select count(1)
from delete_me1 a
where nvl(null,b)=b;
COUNT(1)
--------
3

Thanks in advance.

Rehab

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center