#string #oracle #datetime #plsql #where-clause
#строка #Oracle #дата и время #plsql #where-предложение
Вопрос:
Я немного запутан и понятия не имею, где я допустил ошибку.
SELECT uta.StartDate, uta.EndDate FROM user_timesheets_absence uta
WHERE uta.UserID = 353
AND uta.Approved = 'true'
AND '2020-03-06' BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') AND TO_DATE(uta.EndDate,'YYYY-MM-DD') INTERVAL '1' DAY
В следующем запросе я получаю ошибку
ORA-01722: invalid number
Я пытаюсь понять, в чем ошибка, но когда я понимаю, что запрос глубже, я не вижу, где ошибка.
ABSENCETYPE VARCHAR2(255 CHAR)
ANSWER VARCHAR2(500 CHAR)
APPROVED NUMBER(10,0)
COMMENT_ CLOB
DAYS NUMBER(10,0)
ENDDATE VARCHAR2(50 CHAR)
ISANSWERED NUMBER(10,0)
ISREJECTED NUMBER(10,0)
STARTDATE VARCHAR2(50 CHAR)
USERABSENCEID NUMBER(10,0)
USERHASSEEN VARCHAR2(500 CHAR)
USERID NUMBER(10,0)
WIDTH NUMBER(10,0)
Когда я вставляю данные в таблицу, я использую этот тип данных, и он работает нормально. Но когда я использую SELECT statment, я получаю ошибку. Я надеюсь, что ошибка была идентификатором пользователя, но это не так.
Insert into DB.USER_TIMESHEETS_ABSENCE (ABSENCETYPE,ANSWER,APPROVED,DAYS,ENDDATE,ISANSWERED,ISREJECTED,STARTDATE,USERABSENCEID,USERHASSEEN,USERID,WIDTH) values ('1','ne',0,8,'2020-02-06',1,1,'2020-01-30',89,'1',348,160);
Может кто-нибудь сказать мне, где я допустил ошибку? Что здесь не так?
Ответ №1:
В этом запросе более одной проблемы.
Во-первых, здесь:
AND uta.Approved = 'true'
approved это число, но вы сравниваете его со строкой «true». Это не работает. Используйте буквенное число справа от знака равенства.
Затем, здесь:
AND '2020-03-06'
BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD')
AND TO_DATE(uta.EndDate,'YYYY-MM-DD') INTERVAL '1' DAY
Вы сравниваете строку с датами. Это не сработает. Вероятно, вам нужна буквенная дата в левой части between . Я бы также рекомендовал использовать 1 синтаксис вместо интервала, который должен быть зарезервирован для timestamp s:
AND DATE '2020-03-06'
BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD')
AND TO_DATE(uta.EndDate,'YYYY-MM-DD') 1
Комментарии:
1. Спасибо за ответ 🙂 Я понимаю, где сейчас ошибка 🙂 Я понял, в чем дело: D
2. @GMB У вас есть что-нибудь авторитетное (или близкое) о том, чтобы не добавлять интервалы к датам?
3. Мне тоже было бы интересно узнать, почему вы считаете, что интервальная арифметика должна быть зарезервирована для временных меток. Документация Oracle предполагает, что даты вполне приемлемы.
4. @APC: Я помню, что читал это несколько раз здесь, в SO, но не могу найти фактическую ссылку, и в документации действительно указано иное . Так что вы, вероятно, правы в том, что использование интервалов и даты подходит.
Ответ №2:
Почему STARTDATE и ENDDATE определяются как строки, а не date . Неправильное использование типов данных — верный способ возникновения проблем с повреждением данных. Это, безусловно, то, с чего я бы начал искать.
Другое дело, всегда используйте явное приведение при преобразовании строк в даты (или числа) и наоборот. В этом случае вы должны использовать литерал даты. Вместо
AND '2020-03-06' BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') AND TO_DATE(uta.EndDate,'YYYY-MM-DD')
использовать
AND date '2020-03-06' BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') AND TO_DATE(uta.EndDate,'YYYY-MM-DD')
Если у вас повреждены данные в столбцах даты, и вы используете 12c или более позднюю версию, вы можете использовать VALIDATE_CONVERSATION для фильтрации строк, которые не содержат допустимых дат:
SELECT uta.StartDate, uta.EndDate FROM user_timesheets_absence uta
WHERE uta.UserID = 353
AND uta.Approved = 'true'
AND validate_conversion(uta.StartDate,'YYYY-MM-DD') = 1
AND validate_conversion(uta.EndDate,'YYYY-MM-DD') = 1
AND date '2020-03-06' BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') AND TO_DATE(uta.EndDate,'YYYY-MM-DD') INTERVAL '1' DAY
Вы также можете использовать это для поиска недопустимых строк даты:
SELECT *
FROM user_timesheets_absence uta
WHERE validate_conversion(uta.StartDate,'YYYY-MM-DD') = 0
OR validate_conversion(uta.EndDate,'YYYY-MM-DD') = 0
Комментарии:
1. Интересно. @GMB имеет действительную точку зрения относительно
AND uta.Approved = 'true'