OraFAQ Forum: SQL & PL/SQL » About format for TO

Home » SQL & PL/SQL » SQL & PL/SQL » About format for TO_NUMBER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

About format for TO_NUMBER [message #1175]

Mon, 08 April 2002 08:15

sorcer
Messages: 19
Registered: January 2002

Junior Member

Hello,
I have three fields :
YEAR = '2002'
MONTH = '01'
DAY = '31'
and i want to convert it to number like this :
MY_DATE = 20020131
and i tried with :
SELECT (TO_NUMBER(YEAR, '9999') || TO_NUMBER(MONTH, '99') || TO_NUMBER(DAY, '99')) AS MY_DATE FROM DUAL
but it results :
MY_DATE = 2002311

Can somebedy help me !
Thank you very much.

Report message to a moderator

Re: About format for TO_NUMBER [message #1176 is a reply to message #1175]

Mon, 08 April 2002 08:46

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

WORKS FINE FOR ME!!.
didnt use dual..thats it.

SQL> DESC OO
Name Null? Type
----------------------------------------- -------- ----------------------------
Y VARCHAR2(10)
M VARCHAR2(10)
D VARCHAR2(10)

SQL> SELECT * FROM OO;

Y M D
---------- ---------- ----------
2002 01 31

SQL> SELECT (TO_NUMBER(Y, '9999') || TO_NUMBER(M, '99') || TO_NUMBER(D, '99')) MY_DATE FROM OO;

MY_DATE
--------------------------------------------------------------------------------
2002131

Report message to a moderator