Блог

У меня есть поле типа данных varchar2 ( lmp_date ), которое может возвращать null либо то, что выглядит как значение, либо то, что выглядит как timestamp значение. Изменить базу data_type данных на DATE невозможно, поэтому теперь мне нужно преобразовать ее в дату, но со значениями, возвращаемыми этим столбцом, у меня возникли некоторые проблемы.

Возвращаемые значения для lmp_date = null или 2021-06-11-00.00.00

Необходим формат даты: MM/DD/YYYY

Я пытался cast convert , substr instr но безрезультатно

ETA — Пара примеров попыток (потому что было 10 :

select order_no, to_date(lmp_date) lmp_date from table_a — с сообщением об ошибке «ORA-01861: литерал не соответствует строке формата»

select order_no, to_date(substr(lmp_date, 1, instr(lmp_date, '00' -15))) lmp_date from table_a — поскольку lmp_date у null него есть возможности для создания ценности, это не работает успешно

select order_no, cast(lmp_date as date) lmp_date from table_a — с тем же сообщением об ошибке «ORA-01861: литерал не соответствует строке формата»

select order_no, to_date(lmp_date, 'YYYY-MM-DD') lmp_date from table_a — ORA-01830: изображение в формате даты заканчивается перед преобразованием всей входной строки

Было больше попыток, это все, что я могу вспомнить

Чтобы преобразовать строку в дату, используйте to_date() функцию с подходящей маской формата:

 to_date(lmp_date, 'YYYY-MM-DD-HH24:MI:SS')
 

Элементы модели формата приведены в документации.

Результатом этого является тип данных даты, который представляет собой внутреннее 7-байтовое представление. Ваш клиент или приложение отформатируют это для отображения, что может основываться на ваших настройках NLS_DATE_FORMAT, поэтому вы можете изменить их, чтобы изменить отображение всех дат; или использовать to_char() для преобразования даты обратно в строку, например:

 to_char(to_date(lmp_date, 'YYYY-MM-DD-HH24:MI:SS'), 'MM/DD/YYYY')
 

хотя, если вы хотите, чтобы это была та строка, вы можете просто использовать манипуляции со строками substr() и объединение:

 case when lmp_date is not null then
  substr(lmp_date, 6, 2) || '/' || substr(lmp_date, 9, 2) || '/' || substr(lmp_date, 1, 4)
end
 

db<>скрипка

Когда вы делаете что-либо из этого:

 to_date(lmp_date)
cast(lmp_date as date)
 

это также зависит от вашего сеанса NLS_DATE_FORMAT; и ошибка «литерал не соответствует строке формата» указывает на то, что он не соответствует строке, например, если у вас по-прежнему установлен параметр «DD-MON-RR» по умолчанию. Это действительно сработало бы — для вас в вашей текущей сессии — если бы вы изменили эту настройку. Я показал это здесь только для информации. но чтобы работать с кем угодно, независимо от настроек сеанса, вы должны использовать to_date() явную маску формата и не полагаться и не предполагать ничего, относящегося к конкретному сеансу.

Ты был почти там с:

 to_date(lmp_date, 'YYYY-MM-DD')
 

и снова сообщение «изображение формата даты заканчивается до преобразования всей входной строки» сообщает вам, что не так — ваша строка проходит мимо элементов ГГГГ-ММ-ДД. Расширение маски формата для соответствия всей строке, как я сделал выше, означает, что она знает, что означает каждая часть.

Если бы вас действительно интересовала только часть даты, вы могли бы отрезать конец строки:

 to_date(substr(lmp_date, 1, 10), 'YYYY-MM-DD')
 

но это действительно полезно только в том случае, если у вас есть сочетание строковых значений, где у некоторых есть время, а у некоторых нет. (В результирующей дате всегда будет указано время; это будет просто полночь.) И если у вас есть даты в разных форматах, то это становится немного сложнее — отчасти поэтому вы не должны хранить даты в виде строк.