Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help needed in converting date to number

user497267May 19 2009 — edited May 19 2009
Hi,

I am trying to execute the below query, its failing with "invalid number" error.

select * from process_status
where time_process > to_char('&3','yyyymmdd')

time_process is the "number" datatype.

Details:
---------
SQL> select * from process_status
2 where time_process > to_char('&3','yyyymmdd');
Enter value for 3: 01-MAY-09
old 2: where time_process > to_char('&3','yyyymmdd')
new 2: where time_process > to_char('01-MAY-09','yyyymmdd')
where time_process > to_char('01-MAY-09','yyyymmdd')
*
ERROR at line 2:
ORA-01722: invalid number

If I execute the below query its working fine, I am facing problem only when I pass the date explicitly to the query. Please help me on converting the date to number format.

select * from etl_process_status
where time_process > '20090501'
and rownum < 3;


Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 16 2009
Added on May 19 2009
8 comments
5,699 views