sqloracleto-timestamp

null values not processed by to_timestamp in oracle sql

I'm trying to load data from a csv with sql-loader. There's one column with date in this format:

2011-12-31 00:00:00.000

I tried it using to_date() but it couldn't handle fractions of second. Therefore I used this:

cast(TO_TIMESTAMP(:DATUM_ONTVANGST,      'YYYY-MM-DD HH24:MI:SS.FF3')as date)

Now I get the error:

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

for null values in the column

Can to_timestamp not handle null values or am I doing something wrong?

Solution

  • This works:

    select
cast(TO_TIMESTAMP('2011-12-31 00:00:00.000',      'YYYY-MM-DD HH24:MI:SS.FF3')as date)
from dual

    and this also works

    select
cast(TO_TIMESTAMP(null,      'YYYY-MM-DD HH24:MI:SS.FF3')as date)
from dual

    So there must be some value that is not right format