Varchar2 to Date для сравнения sysdate

В запрашиваемой таблице есть даты, хранящиеся как varchar2. Мне нужно сравнить даты, хранящиеся как varchar2, с sysdate, используя BETWEEN sysdate-1 AND sysdate-30 (чтобы вернуть даты varchar2 за последний месяц).

При указании TO_DATE(varchar2, 'DD-MON-YYYY') я получаю, что литерал ошибки не соответствует строке формата.

Я застрял, поскольку в документации Oracle говорится, что это приемлемый формат для TO_DATE() при преобразовании из varchar2.

ОБНОВЛЕНИЕ: это корпоративная база данных, я не проектировал ее и могу работать только с тем, что у меня есть. Набор данных огромен и автоматически обновляется устройствами SCADA, более 10 000 устройств ежедневно.

SELECT device_name, read_date, sysdate

FROM oracle_database

------- Data Returned by query --------

device_name read_date   sysdate
Device 1    5/14/2013   22-Sep-14
Device 2    5/14/2013   22-Sep-14
Device 3    5/14/2013   22-Sep-14
Device 4    5/14/2013   22-Sep-14
Device 5    5/14/2013   22-Sep-14
Device 6    5/14/2013   22-Sep-14
Device 7    5/14/2013   22-Sep-14

Результаты с использованием TO_DATE

ВЫБЕРИТЕ имя_устройства, TO_DATE (дата_ чтения, 'ДД-МЕС-ГГ'), системная дата

------- Данные, возвращаемые запросом ----------

ORA-01861: литерал не соответствует строке формата


sql oracle sysdate varchar to-date
person tx_query    schedule 22.09.2014    source источник
comment
Можете ли вы привести пример вашего вклада?   -  person crthompson    schedule 22.09.2014
comment
Вы должны использовать формат, соответствующий фактически сохраненным вами строкам. Вы должны надеяться, что они все в одном формате и действительны ... Возможно, есть функция, которая пробует несколько форматов, но это беспорядок, особенно. если у вас есть даты ДД / ММ и ММ / ДД. Вот почему даты не следует хранить в виде строк ... В любом случае, если вы покажете некоторые образцы данных и даты, которые они представляют (если это неочевидно), тогда можно будет предложить подходящую модель.   -  person Alex Poole    schedule 22.09.2014
comment
@Alex, я надеюсь, вы не будете возражать, если я попробую ответить на вопрос, почему не хранить дату в виде строки. Я включил ссылку на статью Эда Стивенса об этом, что довольно круто. И я везде использую одну и ту же ссылку, чтобы отвечать на похожие вопросы.   -  person Lalit Kumar B    schedule 22.09.2014
comment
@LalitKumarB - хотя он и действителен, я не думаю, что он объясняет ошибку, которую видит OP, или что с этим делать. (Помимо сохранения в виде даты; но тогда, возможно, проблема модели переместится во вставку). Но потом, пока формат строк не станет известен, больше не о чем говорить ...   -  person Alex Poole    schedule 22.09.2014
comment
@Alex, я подумал о том, чтобы сказать OP изменить его сеанс с помощью nls_date_format, если он не уверен в настройках nls, но я воздержался, поскольку я любитель, играющий больше в формате уровня запроса, чем на уровне сеанса, и это переопределит настройки уровня сеанса. Давайте посмотрим, что OP сообщает нам о дальнейших деталях. Я постараюсь импровизировать свой ответ на основе новых данных OP.   -  person Lalit Kumar B    schedule 22.09.2014
comment
Вы обязательно должны показать нам небольшой, но важный образец столбца даты. Очевидно, что должны быть некоторые несоответствия между маской, которую вы пытаетесь использовать, и фактическим форматом даты.   -  person Sylvain Leroux    schedule 22.09.2014
comment
Я добавил в комментарий дополнительные отзывы, спасибо @AlexPoole   -  person tx_query    schedule 23.09.2014

Ответы (3)


arrow_upward
1
arrow_downward

Учитывая ваши данные, наиболее вероятно, что правильный формат MM/DD/YYYY. Он будет соответствовать как однозначным, так и двузначным месяцам или дням.

  • MM: месяц с 01 по 12 (начальные 0 по умолчанию не обязательны)
  • \: любой знак препинания
  • DD: день с 01 по 31 (начальные 0 по умолчанию не обязательны)
  • \: любой знак препинания
  • YYYY: год

См. документацию Oracle по моделям формата datetime для Детали.

Вот пример:

-- Some test data
WITH testdata AS (
  SELECT '5/14/2013' as d FROM DUAL
  UNION SELECT '05/14/2013' FROM DUAL
  UNION SELECT '5/1/2013' FROM DUAL
  UNION SELECT '5/01/2013' FROM DUAL
  UNION SELECT '6-6-2013' FROM DUAL)

-- Actual query demonstrating the use of the MM/DD/YYYY format
select d, TO_DATE(d,'MM/DD/YYYY') FROM testdata

Производство:

D           TO_DATE(D,'MM/DD/YYYY')
05/14/2013  05/14/2013
5/01/2013   05/01/2013
5/1/2013    05/01/2013
5/14/2013   05/14/2013
6-6-2013    06/06/2013

Если вам действительно необходимо обеспечить разделение компонентов даты знаком / (а не каким-либо другим знаком препинания), вместо этого следует использовать fxfmMM/DD/YYYY:

  • флаг fx приводит к строгому сравнению как по пунктуации, так и по количеству цифр в формате
  • флаг fm ослабляет это сравнение, чтобы позволить увеличить количество указанных цифр
person Sylvain Leroux    schedule 22.09.2014
comment
Как мне присоединиться к подзапросу testdata с таблицей моих устройств? Я не могу использовать дату, так как она не уникальна. Я прочитал документацию Oracle, относящуюся к WITH, но все еще рисую пробел. Кроме того, с учетом вышеизложенного не представляется возможным вернуть в подзапросе какие-либо данные за пределами диапазона дат. Например: ГДЕ sysdate МЕЖДУ (sysdate-1) И (sysdate-30) - person tx_query; 23.09.2014
comment
@tx_query - testdata просто для того, чтобы предоставить несколько примеров данных, подобных вашим, и показать, как они успешно конвертируются в дату с этой моделью формата. Вам не нужно testdata или присоединение. Просто используйте TO_DATE(read_date, 'MM/DD/YYYY') в исходном запросе. - person Alex Poole; 23.09.2014
comment
@AlexPoole Как бы то ни было, я обнаружил, что формат был yyyy-mm-dd и выбрал ROUND (avg (valve_pressure) over (PARTITION BY unique_id order by to_date (read_date, 'yyyy-mm-dd') в диапазоне от 30) предыдущая и текущая строки)) MA_30 Большое спасибо вам двоим за ваши быстрые и вежливые ответы. - person tx_query; 23.09.2014

arrow_upward
0
arrow_downward

Во-первых, ваш дизайн ошибочен. Вы никогда не должны использовать DATE как VARCHAR2 тип данных.

Во-вторых, при сравнении дат и преобразовании date literal в DATE всегда указывайте одну и ту же маску формата с обеих сторон.

Чтобы преобразовать строку в дату, используйте TO_DATE с правильной маской формата.

Чтобы преобразовать дату в строку, используйте TO_CHAR с той же маской формата.

Убедитесь, что при сравнении значений в выражении вы explicitly конвертируете тип данных по обе стороны от comparison operator, чтобы избежать implicit data type conversion.

И вы должны прочитать эту прекрасную статью бэда Стивенса, http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

person Lalit Kumar B    schedule 22.09.2014

arrow_upward

arrow_downward

person    schedule
comment
Ответ может стать более ценным, если вы добавите некоторые пояснения и ссылки на документацию, чтобы исходный постер и другие пользователи действительно могли извлечь из него уроки. - person Markus Safar; 14.02.2016