ORA-01841: (full) year must be between -4713 and +9999

[ray.wurlod]

What do you get in the text file if you don't use the TO_CHAR function?

[AbhinavSharma]

Hi, I remember getting this error while using TO_DATE() in a stored procedure a while ago:

"ORA-01841: (full) year must be between -4713 and +9999, and not be 0"

The issue is because TO_DATE() expects to work on a CHARACTER data type and so there is an implicit conversion from DATE to CHAR.

For the example whenever there is a date value with year 2000, it is implicitly converted to the character string "date-month-00",
which is then converted back to a date using a DD-MON-YYYY date format. This gives a year of 0000 which is invalid, hence you get an error ORA-1841.

Hope this helps