ORA-01841 while using TO_DATE with null values

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 converting char to DATE. This clause has no effect if an error occurs while evaluating char. The return_value can be an expression or a bind variable, and it must evaluate to a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, or null. The function converts return_value to DATE using the same method it uses to convert char to DATE. If return_value cannot be converted to DATE, then the function returns an error.