null values not processed by to_timestamp in oracle sql

Question

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?

Answer 1

I think there is space(<field>, ,<field>) in your data which is causing the issue.

Better to use TRIM before using them.

Following are some examples to demonstrate with different values:

-- working fine with correct timestamp values
SQL> SELECT
  2      CAST(TO_TIMESTAMP('2019-11-05 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE) as dt
  3  FROM
  4      DUAL;

DT
---------
05-NOV-19

-- working fine with a null value
SQL>
SQL> SELECT
  2      CAST(TO_TIMESTAMP(NULL, 'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE) as dt
  3  FROM
  4      DUAL;

DT
---------



-- working fine with an empty value
SQL>
SQL> SELECT
  2      CAST(TO_TIMESTAMP('', 'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE) as dt
  3  FROM
  4      DUAL;

DT
---------

Now showing the error and solution

-- giving an error with space in the value -- Your case 
-- might be you are considering it as the null 
-- but it is actually a value that is a space character
SQL>
SQL> SELECT
  2      CAST(TO_TIMESTAMP(' ', 'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE) as dt
  3  FROM
  4      DUAL;
    CAST(TO_TIMESTAMP(' ', 'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE) as dt
                      *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


-- Solution to your issue -- using TRIM
SQL>
SQL> SELECT
  2      CAST(TO_TIMESTAMP(TRIM(' '), 'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE) as dt
  3  FROM
  4      DUAL;

DT
---------


SQL>

Cheers!!

Answer 2

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

Answer 3

Building off Ersin's answer, this is the format that worked for me

select
cast(NULL AS TIMESTAMP WITH LOCAL TIME ZONE) as date
from dual