ORA-01841: (full) year must be between -4713 and +9999, and not be 0
First example we use the year 0, which is not a valid year:
SQL> select to_date('00001231', 'YYYYMMDD') from dual;
select to_date('00001231', 'YYYYMMDD') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
The second test is with a year lower than -4713
SQL> select to_date('-47141231', 'YYYYMMDD') from dual;
select to_date('-47141231', 'YYYYMMDD') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
So to be valid, the year need to be between -4713 and 9999, and not be zero:
SQL> select to_date('20061231', 'YYYYMMDD') from dual;
TO_DATE('
---------
31-DEC-06
Welcome to our forum for Oracle error: ORA-01841 Add your own message
Getting this error in oracle
ORA-01841; full year must be between -4713 and +9999 andbelow is my sql statement.l How can I fix the error
to_char(to_date(text4, case when instr(text4,'/') >0 then 'MM/DD/YYYY' else 'YYYY-MM-DD' end),'MM-DD-YYYY')
It looks like you try to just replace the / into a -
Is this the case? If so you can just use replace(text4, '/', '-')
If not, why are you converting back to a character?
As for the error, you need to check the value for text4. Maybe there is a value like 00-00-0000 inside?
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.