ORA-01843: ошибка неверного месяца

У меня есть столбец в Oracle DB с типом данных varchar2. Типичное значение, хранящееся в этом столбце, похоже на 06/16/2015 02:14:18 AM. Я пытаюсь получить все записи, в которых в этом столбце есть записи после 1 августа 2015 года.

select * 
from MYTABLE 
where to_date(substr(MYCOLUMN,1,10),'dd-mm-yyyy') > to_date('01-08-2015','dd-mm-yyyy');

Но я получаю ORA-01843. Где я делаю не так?


sql date oracle date-comparison
person user2488578    schedule 28.08.2015    source источник
comment
Формат в столбце: 06/16/2015 02:14:18 AM не соответствует маске формата dd-mm-yyyy, которую вы используете в своем to_date() вызове этого столбца. Вы хотите mm/dd/yyyy вместо   -  person a_horse_with_no_name    schedule 28.08.2015
comment
Ваша скороговорка неверна: ´'dd-mm-yyyy 'должно быть mm/'dd/yyyy'   -  person Jens    schedule 28.08.2015
comment
Почему вы храните DATE как VARCHAR2? Использование соответствующего типа данных - одна из наиболее важных частей проектирования и производительности базы данных.   -  person Lalit Kumar B    schedule 28.08.2015
comment
Эта колонна была создана давно. Изменение типа данных этого столбца сейчас добавит слишком много других накладных расходов.   -  person user2488578    schedule 28.08.2015
comment
@ user2488578 Изменение типа данных этого столбца сейчас добавит слишком много других накладных расходов. Поверьте, рано или поздно вам придется это сделать. Никогда не поздно исправить проблему с дизайном.   -  person Lalit Kumar B    schedule 28.08.2015

Ответы (3)


arrow_upward
3
arrow_downward

Уважайте формат в вашем VARCHAR

....where to_date(substr(MYCOLUMN,1,10),'mm/dd/yyyy')
person Mihai    schedule 28.08.2015
comment
Спасибо. Но я получаю ту же ошибку, если добавляю заказ до .._ 1_ - person user2488578; 28.08.2015

arrow_upward
1
arrow_downward

У меня есть столбец в Oracle DB с типом данных varchar2. Типичное значение, хранящееся в этом столбце, похоже на 16.06.2015 02:14:18 AM.

Первый вопрос: почему вы храните ДАТУ как строку? Использование соответствующего типа данных - одна из наиболее важных частей дизайна базы данных и производительности.

Помните, что DATE не имеет того формата, который вы видите, он хранится внутри в 7 bytes, который является проприетарным форматом Oracle. Не рекомендуется хранить дату в виде строки, чтобы иметь фиксированный формат.

Я бы посоветовал сначала исправить дизайн, чтобы вам не приходилось выполнять эти накладные расходы при сравнении дат. В долгосрочной перспективе это поможет вам.

1. Добавьте новый столбец как тип данных DATE.

ALTER TABLE table_name 
   ADD new_column DATE;

2. Обновите новый столбец.

UPDATE table_name 
   SET new_column = TO_DATE(old_column, 'mm/dd/yyyy hh:mi:ss pm');

3. УДАЛИТЬ старый столбец.

ALTER TABLE table_name 
   DROP COLUMN old_column;

4. Переименуйте новый столбец в старое имя столбца.

ALTER TABLE table_name
  RENAME COLUMN old_name to new_name;

Теперь вы можете легко сравнивать даты:

SELECT * FROM MYTABLE WHERE mycolumn > to_date('01-08-2015','dd-mm-yyyy');

Это также будет использовать любой обычный индекс в столбце даты.

С точки зрения производительности:

Если вы не исправите это сейчас, вы будете продолжать сталкиваться с проблемами производительности. Поскольку немедленное исправление SUBSTR не позволит вам использовать обычный индекс, вам необходимо создать функциональный индекс.

person Lalit Kumar B    schedule 28.08.2015

arrow_upward
0
arrow_downward

Если в вашей таблице все значения, подобные 06/16/2015 02:14:18 AM, вы можете использовать trunc(to_date(MYCOLUMN,'mm/dd/yyyy HH:mi:SS PM'),'dd') против to_date(substr(MYCOLUMN,1,10),'dd-mm-yyyy').

person Stawros    schedule 28.08.2015