OraFAQ Forum: SQL & PL/SQL » Getting Error

Home » SQL & PL/SQL » SQL & PL/SQL » Getting Error - ORA-01858 (Windows 10)

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

Getting Error - ORA-01858 [message #688116]

Fri, 25 August 2023 14:24

Qrry
Messages: 1
Registered: August 2023

Junior Member

Greetings. I'm getting Error - ORA-01858: a non-numeric character was found where a numeric was expected when I run the following query.

What I'd love it to do is call "today's date" every time I run it so that I don't have to modify the date manually each time I'd like to view the fetched data.

((status = 'APPR' or status = 'WAPPR') 
and siteid = 'BSC01' 
and reportdate >= to_date('CURRENT_DATE()', 'YYYY-MM-DD HH24:MI:SS') 
and worktype = 'RD' 
and location like '%88-019%')

Any help you guys could provide would be much appreciated.

Report message to a moderator

Re: Getting Error - ORA-01858 [message #688117 is a reply to message #688116]

Fri, 25 August 2023 14:58

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Current_date is already a date:

SCOTT@orcl_12.1.0.2.0> select CURRENT_DATE from dual
2 /

CURRENT_DATE
---------------
Fri 25-Aug-2023

If you try to use to_date on a date it is an unnecessary step that usually causes some error:

SCOTT@orcl_12.1.0.2.0> select c from dual
2 /
select to_date(CURRENT_DATE, 'YYYY-MM-DD HH24:MI:SS') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

To convert a date to characters and display it in a particular format, use to_char:

SCOTT@orcl_12.1.0.2.0> select to_char(CURRENT_DATE, 'YYYY-MM-DD HH24:MI:SS') from dual
2 /

TO_CHAR(CURRENT_DAT
-------------------
2023-08-25 12:46:40

1 row selected.

If you do not specify a format using to_char, then it uses whatever the current nls_date_format is:

SCOTT@orcl_12.1.0.2.0> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
2 /

Session altered.

SCOTT@orcl_12.1.0.2.0> select CURRENT_DATE from dual
2 /

CURRENT_DATE
-------------------
2023-08-25 12:51:56

1 row selected.

If your reportdate is a date datatype as it should be, then you should just compare dates and format is irrelevant.

and reportdate >= CURRENT_DATE

Report message to a moderator

Previous Topic:	Data Extraction
Next Topic:	Remove duplicates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 02:05:36 CDT 2024