Преобразование дат, хранящихся как VARCHAR2, в дату

Я видел подобные сообщения, но я не могу решить свой вопрос.

Я пытаюсь запросить таблицу со столбцом («ЗНАЧЕНИЕ») типа данных VARCHAR2.

Строки в этом столбце смешаны как с числовыми значениями, так и со значениями даты (я не знаю, почему даты были сохранены как VARCHAR2).

Мне нужны только даты, и я отфильтровал строки с помощью функции LIKE.

SELECT
PARENTID,
NAME,
VALUE
FROM TIMINGEVENT
WHERE NAME like 'last%'
;

Теперь в столбце есть только даты, и мне нужно преобразовать VARCHAR2 в дату.

PARENTID        ++  NAME            ++  VALUE 

1701480         ++  lastCycle1      ++  

1701480         ++  lastCycle2      ++  

1701480         ++  lastCycle3      ++  20150901092520 AM

1701480         ++  lastCycle4      ++  20150901092834 AM

1701480         ++  lastCycle5      ++  20150901085047 AM

Мои попытки использовать TO_DATE привели к следующей ошибке:

ORA-01858: вместо числового символа был найден нечисловой

Я использую Oracle 11g SQL Developer, а в настройках NLS для формата даты установлено значение DD-MON-RR.

Я нашел приведенный ниже подход в другом сообщении, но когда я его использую, он выдает следующую ошибку?

SELECT 
PARENTID,
NAME,
VALUE,
TO_CHAR(TO_DATE(VALUE, 'MM/DD/YYYY'), 'MM/DD/YYYY') AS "test"
FROM TIMINGEVENT
WHERE NAME like 'last%'
;


ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

sql date oracle11g to-date varchar2
person jrh11570    schedule 24.05.2016    source источник
comment
Какова была ваша попытка использовать to_date? Вы указали маску формата или полагались на настройки NLS?   -  person Alex Poole    schedule 24.05.2016
comment
Честно говоря, оба. Я прокручивал несколько масок и тоже полагался на настройки. Я продолжал получать различные ошибки, в том числе: ORA-01861: литерал не соответствует строке формата.   -  person jrh11570    schedule 24.05.2016
comment
Я обновил свой вопрос выше другим примером. Пожалуйста, помогите, спасибо.   -  person jrh11570    schedule 25.05.2016
comment
Эта маска формата не соответствует вашим строкам varchar2. Почему вы не используете ответ Алексея?   -  person Alex Poole    schedule 25.05.2016

Ответы (4)


arrow_upward
1
arrow_downward

Кажется, вам нужен только правильный формат:

with test(parentId, name, value) as (
select '1701480','lastCycle1','' from dual union all
select '1701480','lastCycle2','' from dual union all
select '1701480','lastCycle3','20150901092520 AM' from dual union all
select '1701480','lastCycle4','20150901092834 AM' from dual union all
select '1701480','lastCycle5','20150901085047 AM' from dual
)
select to_date(value, 'YYYYMMDDHHMISS AM')
from test
person Aleksej    schedule 24.05.2016

arrow_upward
0
arrow_downward

Когда вы используете спецификатор формата внутри to_date(), он пытается отобразить значение в столбце точно в формате, который был в качестве спецификатора формата, а не меньше или больше.

Итак, когда вы используете TO_DATE(VALUE, 'MM/DD/YYYY'), он пытается отобразить первые 2 символа, т.е. 20, как MM. Следовательно, он дает ошибку как not a valid month.

Вам нужен правильный спецификатор формата для работы со столбцом, как показано ниже:

ВЫБЕРИТЕ TO_DATE(VALUE, 'ггггммддччмисс утра') ИЗ СОБЫТИЯ ВРЕМЕНИ

Это даст вывод, как -

9/1/2015 9:25:20 AM

Позже вы можете снова отформатировать вывод этого в соответствии с вашими требованиями, используя to_char и снова используя соответствующий спецификатор формата.

SELECT to_char(TO_DATE(VALUE, 'ггггммддччмисс am'),'DD-MON-YYYY HH12:MI:SS AM') ИЗ TIMINGEVENT

Это даст результат вроде -

01 сентября 2015 г., 09:25:20

Обратите внимание, не имеет значения, содержит ли ваше значение am или pm, вы можете использовать как am, так и pm. Я имею в виду, что по иронии судьбы не обязательно использовать «am», если значение содержит «am». Вы также можете использовать «pm», даже если значение содержит «am». Не то чтобы это имело смысл использовать его таким образом, но все же просто для вашего сведения.

person SubhasisM    schedule 26.05.2016

arrow_upward
0
arrow_downward

Сначала разберитесь с использованием функций to_date и to_char:

TO_CHAR: чтобы изменить другие типы данных, такие как дата или число, на строку, если вы меняете дату на символ, вы должны указать формат даты, в который она должна быть преобразована.

TO_DATE Чтобы изменить строку/символ на дату, вторым параметром является формат даты вашей строки в вашем случае, формат даты строки «20150901092520 AM» — «ГГГГММДДЧЧМИСС AM», поэтому вы должны использовать его в виде

to_date('20150901092520 AM', 'YYYYMMDDHHMISS AM'), это преобразует его в объект даты, теперь, чтобы напечатать требуемый формат как «01/09/2015», используйте to_char и укажите формат как «MM/DD/YYYY», например ниже

to_char(to_date('20150901092520 AM', 'ГГГГММДДЧЧМИСС AM'),'ММ/ДД/ГГГГ')

person Shoban Sundar    schedule 27.05.2016

arrow_upward
-2
arrow_downward

Вы должны иметь возможность использовать функцию ISDATE для проверки строки перед преобразованием https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1106.htm

В общем... SELECT cust_last_name, CASE value WHEN isdate(value) = 'Yes' THEN convert(date,value) WHEN isdate(value) <> 'Yes' THEN '' END FROM TABLE;

person Aron    schedule 24.05.2016
comment
Этот синтаксис недействителен в Oracle SQL. - person Alex Poole; 24.05.2016
comment
Вот что я получаю за отказ от MSSQL... ха-ха. Извини :( - person Aron; 24.05.2016