I have a date column that is formatted as number(10) data type. Some records contain -1 values (null). I would like to format that column to date type. Using the TO_DATE
function returns this message:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Is this caused by the null values? How can avoid that?
TO_DATE(TEST.PRELIM_DATE,'YYYYMMDD')`
Thank you
Oracle function TO_DATE
does accept NULL
values (and returns NULL
in that case). The error that you are getting means that the given value does not match the specified format.
If -1
is the only invalid value that might show up, just replace it with NULL
, like :
TO_DATE(
NULLIF(TEST.PRELIM_DATE, -1),
'YYYYMMDD'
)
Starting with Oracle 12.2, another solution is to use the ON CONVERSION ERROR
clause, like :
TO_DATE(
TEST.PRELIM_DATE DEFAULT NULL ON CONVERSION ERROR,
'YYYYMMDD'
)
This will trap all conversion errors and return NULL
instead of raising an error (note that this can lead to legitimate errors being masked). From the documentation :
The optional
DEFAULT return_value ON CONVERSION ERROR
clause allows you to specify the value this function returns if an error occurs while convertingchar
toDATE
. This clause has no effect if an error occurs while evaluatingchar
. Thereturn_value
can be an expression or a bind variable, and it must evaluate to a character string ofCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
data type, ornull
. The function convertsreturn_value
toDATE
using the same method it uses to convertchar to
, then the function returns an error.DATE
. Ifreturn_value
cannot be converted to DATE