нулевые значения не обрабатываются to_timestamp в oracle sql

Question

Я пытаюсь загрузить данные из csv с помощью sql-loader. Есть один столбец с датой в таком формате:

2011-12-31 00: 00: 00.000

Я пробовал использовать to_date(), но он не мог обрабатывать доли секунды. Поэтому я использовал это:

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

Теперь я получаю ошибку:

ORA-01841: (полный) год должен быть от -4713 до +9999, а не 0

для нулевых значений в столбце

Может ли to_timestamp не обрабатывать нулевые значения или я что-то делаю не так?

Answer 1

Это работает:

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

и это тоже работает

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

Значит, должно быть какое-то значение не в правильном формате

Answer 2

Я думаю есть space(<field>, ,<field>) в ваших данных, что вызывает проблему.

Лучше использовать TRIM перед их использованием.

Ниже приведены несколько примеров для демонстрации с разными значениями:

-- 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
---------

Теперь показаны ошибка и решение

-- 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>

Ура!!

Answer 3

Основываясь на ответе Эрсина, это формат, который работал у меня.

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