TO_DATE conversion issue [message #606285] |
Wed, 22 January 2014 00:53 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi all,
If I run the below query it returning "ORA-01858: a non-numeric character was found where a numeric was expected" error but If I removed the Where condition it returning result. The both select clause and where clause has same date conversion format then why it's not working in where clause. Please help me to understand why it's not working in Where clause.
The user_value column is VARCHAR2 data type
SELECT TO_DATE(user_value,'mm/dd/yyyy')
FROM user_detail
where TO_DATE(user_value,'mm/dd/yyyy') < ADD_MONTHS(TRUNC(SYSDATE),-2);
And The NLS_DATE_FORMAT is DD-MON-RR . That query working in production database. In Non- Production database only it's not working. All the environments has same NLS_DATE_FORMAT
Thank You
|
|
|
|
|
|
|
|
|
|
Re: TO_DATE conversion issue [message #606313 is a reply to message #606285] |
Wed, 22 January 2014 03:59 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Wed, 22 January 2014 12:23
The user_value column is VARCHAR2 data type
The best solution to avoid future issue is to :
1. Add a new column "user_value_new" as Datatype DATE.
2. Update this column as :
update user_detail set user_value_new=to_date(user_value,'mm/dd/yyyy');
3. Drop old column user_value.
4. Rename new column to user_value.
|
|
|
Re: TO_DATE conversion issue [message #606355 is a reply to message #606294] |
Wed, 22 January 2014 13:34 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
mist598 wrote on Wed, 22 January 2014 02:32
But When I tried to find out the bad data which is causing using below query, it's not returning any mismatch data.
Then I would just repeat what Littlefoot asked.
|
|
|