Home » SQL & PL/SQL » SQL & PL/SQL » TO_DATE conversion issue
TO_DATE conversion issue [message #606285] Wed, 22 January 2014 00:53 Go to next message
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 #606286 is a reply to message #606285] Wed, 22 January 2014 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the type of "user_value" column?

Re: TO_DATE conversion issue [message #606287 is a reply to message #606286] Wed, 22 January 2014 01:01 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
What is the type of "user_value" column?

The user_value column is VARCHAR2 data type
Re: TO_DATE conversion issue [message #606288 is a reply to message #606287] Wed, 22 January 2014 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that not all USER_VALUE values match MM/DD/YYYY format. Yet another example of problems caused by storing date values into character datatype columns.
Re: TO_DATE conversion issue [message #606290 is a reply to message #606288] Wed, 22 January 2014 01:10 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot thank you for reply,

Quote:
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.


Could you please explain more.
Re: TO_DATE conversion issue [message #606292 is a reply to message #606290] Wed, 22 January 2014 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mist598

But If I removed the Where condition it returning result.

Please, post a SQL*Plus session which proves that.
Re: TO_DATE conversion issue [message #606293 is a reply to message #606287] Wed, 22 January 2014 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the result of the following query:
select user_value from t where not regexp_like(user_value,'^\d\d/\d\d/\d\d\d\d$');


Re: TO_DATE conversion issue [message #606294 is a reply to message #606293] Wed, 22 January 2014 01:32 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
The below where clause condition worked for me.

SELECT TO_DATE(user_value,'mm/dd/yyyy')
FROM user_detail
where to_date(regexp_substr (user_value,'^[0-9]{2}/[0-9]{2}/[0-9]{4}$'), 'MM/DD/YYYY') < ADD_MONTHS(TRUNC(SYSDATE),-2);

But When I tried to find out the bad data which is causing using below query, it's not returning any mismatch data. I am trying to understand what is happening. Thanks for your help Michel Cadot & Littlefoot Smile

SELECT TO_DATE(user_value,'mm/dd/yyyy')
FROM user_detail
where user_value <> to_date(regexp_substr (user_value,'^[0-9]{2}/[0-9]{2}/[0-9]{4}$'), 'MM/DD/YYYY');

Re: TO_DATE conversion issue [message #606313 is a reply to message #606285] Wed, 22 January 2014 03:59 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: how to improve sql & Skills
Next Topic: Concatenate issue
Goto Forum:
  


Current Time: Fri Mar 29 07:39:27 CDT 2024