Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01858:, a non-numeric character was found where a numeric was expected.
ORA-01858:, a non-numeric character was found where a numeric was expected. [message #118480] Thu, 05 May 2005 09:26 Go to next message
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
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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
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 Go to previous message
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
Previous Topic: Constraints
Next Topic: I need a pivot query?
Goto Forum:
  


Current Time: Fri Mar 29 06:53:03 CDT 2024