SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not
265441Oct 6 2011 — edited Oct 12 2011Have a field called order_date varchar2(9) in a table. Legacy data and yes I know it should be in date format.
Some of the data in the field is YDDD format and want to convert it to DD-MON-YY
I have tried this:
UPDATE MIFL_STG
SET ORDER_DATE = to_date(ORDER_DATE,'YDDD')
WHERE length(order_date) = 4 ;
and this:
UPDATE MIFL_STG
SET ORDER_DATE = to_char(to_date(ORDER_DATE,'YDDD'),'DD-MON-YY')
WHERE length(order_date) = 4 ;
And get the following error:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
Not sure how to fix this.