OraFAQ Forum: SQL & PL/SQL » ORA-01858:, a non-numeric character was found where a numeric was expected.

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01858:, a non-numeric character was found where a numeric was expected.

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

ORA-01858:, a non-numeric character was found where a numeric was expected. [message #118480]

Thu, 05 May 2005 09:26

paladuna
Messages: 2
Registered: May 2005

Junior Member

Hello experts

I am using following Qry

select * from t_app_email where date_to_send= to_date(sysdate,'mm/dd/yyyy')

getting - ORA-01858:, a non-numeric character was found where a numeric was expected.

here date_to_send is column of type date.

as workaround using between sysdate+1 & syadate-1, but whats the problem with above

Pls help in resolving!

Thanks in advance

Report message to a moderator

Re: ORA-01858:, a non-numeric character was found where a numeric was expected. [message #118485 is a reply to message #118480]

Thu, 05 May 2005 09:51

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

sysdate is already a DATE type. You do not need to do a to_date on it.
Be warned that you will most likely get 0 rows returned when you compare to a date filed, as time is stored with Oracle dates. You will either have to TRUNCate the database column or do a date range as you have seen.

Here are some examples:

WHERE TRUNC(db_column) = TO_DATE('01-JAN-2005')

WHERE TRUNC(db_column) = TRUNC(sysdate)

WHERE db_column between TRUNC(sysdate) and TRUNC(sysdate) + 1

Also note that using sysdate+1 and sysdate-1 like you tried will give you results you might not want, as you will be grabbing a 48 hour window based on the exact hour/minute/second that you run the query.

For example, at 9:47:42am on 5/5/05 (hey wow, look at all those 5's in the date) you will get 5/4/05 9:47:42am through 5/6/05 9:47:42am.

Report message to a moderator

Re: ORA-01858:, a non-numeric character was found where a numeric was expected. [message #118488 is a reply to message #118480]

Thu, 05 May 2005 09:53

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

TO_DATE of a date column doesn't make sense:

SQL> SELECT TO_DATE(sysdate,'mm/dd/yyyy') FROM dual;
SELECT TO_DATE(sysdate,'mm/dd/yyyy') FROM dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> SELECT TO_CHAR(sysdate,'mm/dd/yyyy') FROM dual;

TO_CHAR(SY
----------
05/05/2005

Best regards.

Frank

Report message to a moderator

Re: ORA-01858:, a non-numeric character was found where a numeric was expected. [message #118493 is a reply to message #118485]

Thu, 05 May 2005 10:24

paladuna
Messages: 2
Registered: May 2005

Junior Member

thnx all

'am using
dbcolumn = TRUNC(sysdate) as my dbcolumn is already truncated before storing.

Thank you joy_division

Report message to a moderator

Previous Topic:	Constraints
Next Topic:	I need a pivot query?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 13:51:27 CST 2024