Prev: ORA-06502: PL/SQL: numeric or value error: character stringbuffer too small
Next: Multiple Requirements
From: riverdance on 8 Jun 2010 10:45
HI,

sorry , I'm new to oracle world..


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?



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
From: S. Anthony Sequeira on 8 Jun 2010 10:52
On 08/06/10 15:45, riverdance wrote:
> HI,
>
> sorry , I'm new to oracle world..
>
>
> 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?
>
>
>
> 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

Do the math, look at date formats in the SQL Language reference, also
see the TO_DATE function.

1 / 1 / 2010 = .0004975124
1 - 1 - 2010 = -2010


--
S. Anthony Sequeira
++
i'm living so far beyond my income that we may almost be said to be
living apart.
-- e. e. cummings
++
From: ddf on 8 Jun 2010 11:58
Comments embedded.
On Jun 8, 10:45 am, riverdance <esthershe...(a)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 to
return 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-
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>


David Fitzjarrell
From: Mark D Powell on 8 Jun 2010 12:08
On Jun 8, 10:52 am, "S. Anthony Sequeira" <nob...(a)127.0.0.1> wrote:
> On 08/06/10 15:45, riverdance wrote:
>
>
>
>
>
> > HI,
>
> > sorry , I'm new to oracle world..
>
> > 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?
>
> > 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
>
> Do the math, look at date formats in the SQL Language reference, also
> see the TO_DATE function.
>
> 1 / 1 / 2010 = .0004975124
> 1 - 1 - 2010 = -2010
>
> --
> S. Anthony Sequeira
> ++
> i'm living so far beyond my income that we may almost be said to be
> living apart.
>                 -- e. e. cummings
> ++- Hide quoted text -
>
> - Show quoted text -

Yes, in hind site the answer should be obvious. I will suggest
replacing NVL2 with the ANSI standard coalesce function which will
return the first non-null value in a list of expressions:

SQL> select coalesce(to_char(sysdate,'MM/DD/YYYY'),'01/02/2010'),
2 coalesce(to_char(null,'MM/DD/YYYY'),'01/01/2010')
3 from dual;

COALESCE(T COALESCE(T
---------- ----------
06/08/2010 01/01/2010

HTH -- Mark D Powell --


From: riverdance on 8 Jun 2010 13:02
On Jun 8, 10:45 am, riverdance <esthershe...(a)yahoo.com> wrote:
> HI,
>
> sorry , I'm new to oracle world..
>
> 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?
>
> 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

thanks. it's and expression, not date here. Thanks.
 |  Next  |  Last
Pages: 1 2
Prev: ORA-06502: PL/SQL: numeric or value error: character stringbuffer too small
Next: Multiple Requirements