|
Re: Getting Error - ORA-01858 [message #688117 is a reply to message #688116] |
Fri, 25 August 2023 14:58 |
|
Barbara Boehmer
Messages: 9097 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
|
|
|