Re: about nvl2 function
Date: Tue, 8 Jun 2010 08:58:08 -0700 (PDT)
Message-ID: <92ae384f-fb75-4ba9-a1e4-d20bc4e5c3db_at_y18g2000prn.googlegroups.com>
Comments embedded.
On Jun 8, 10:45 am, riverdance <esthershe..._at_yahoo.com> wrote:
> HI,
>
> sorry , I'm new to oracle world..
>
No need to apologize.
> who could help explain following.. I expected following SQL would
> error out.. but I got following result.
>
> I don't understand, how date format string '1/1/2010' could convert
> to number .000497512, and how date format string 1-1-2010 could
> convert to number -2010?
>
They're not strings, they are numeric calculations:
1/1/2010 == 1 divided by 1 divided by 2010 == .000497512
1-1 2010 == 1 minus 1 minus 2010 == -2010
> SQL> select nvl2(birth_dt,0,1/1/2010) from eshen.test;
>
> NVL2(BIRTH_DT,0,1/1/2010)
> -------------------------
> .000497512
> .000497512
>
> SQL> select nvl2(birth_dt,0,1-1-2010) from eshen.test;
>
> NVL2(BIRTH_DT,0,1-1-2010)
> -------------------------
> -2010
> -2010
Date strings would be '1/1/2010' or '1-1-2010', including the single quotes, and would be used with the to_date function:
SQL> create table test(name varchar2(30), birth_dt date);
Table created.
SQL>
SQL> begin
2 for i in 1..100 loop
3 if mod(i,2) = 0 then
4 insert into test(name, birth_dt)
5 values('Narmo'||i, sysdate - (10*i));
6 else
7 insert into test(name)
8 values('Narmo'||i);
9 end if;
10 end loop;
11
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- These error out with proper dates as return values
SQL> --
SQL> select nvl2(birth_dt,0,to_date('1/1/2010', 'mm/dd/rrrr')) from
test;
select nvl2(birth_dt,0,to_date('1/1/2010', 'mm/dd/rrrr')) from test
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
SQL> select nvl2(birth_dt,0,to_date('1-1-2010', 'mm-dd-rrrr')) from
test;
select nvl2(birth_dt,0,to_date('1-1-2010', 'mm-dd-rrrr')) from test
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
SQL> --
SQL> -- These don't error but don't return the value you wanted
SQL> --
SQL>
SQL> select nvl2(birth_dt,sysdate,to_date('1/1/2010', 'mm/dd/rrrr'))
from test;
NVL2(BIRT
01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10
NVL2(BIRT
08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10
...
100 rows selected.
SQL> select nvl2(birth_dt,sysdate,to_date('1-1-2010', 'mm-dd-rrrr')) from test;
NVL2(BIRT
01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10
NVL2(BIRT
08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10 01-JAN-10 08-JUN-10
...
100 rows selected.
SQL> SQL> -- SQL> -- These give you the 0 you want but have more gyrations toreturn the date you coded and do so in Julian format
SQL> -- as that 'date' is actually a number
SQL> --
SQL> select nvl2(birth_dt,0,to_number(to_char(to_date('1/1/2010', 'mm/
dd/rrrr'),
'J'))) from test;
NVL2(BIRTH_DT,0,TO_NUMBER(TO_CHAR(TO_DATE('1/1/2010','MM/DD/ RRRR'),'J')))
2455198
0
2455198
0
2455198
0
2455198
0
2455198
0
2455198
...
100 rows selected.
SQL> select nvl2(birth_dt,0,to_number(to_char(to_date('1-1-2010', 'mm-
-rrrr'),
'J'))) from test;
NVL2(BIRTH_DT,0,TO_NUMBER(TO_CHAR(TO_DATE('1-1-2010','MM-DD- RRRR'),'J')))
2455198
0
2455198
0
2455198
0
2455198
0
2455198
0
2455198
...
100 rows selected.
SQL> David Fitzjarrell Received on Tue Jun 08 2010 - 10:58:08 CDT