Сравнение дат возвращает необычный результат - SQL Oracle

У меня есть таблица структуры:

+---------+--------------+-----------------+---------------+-------+------+
| week_no | long_week_no | week_start_date | week_end_date | month | year |
+---------+--------------+-----------------+---------------+-------+------+
| 1       | 1A           | 01/01/2015      | 03/01/2015    | JAN   | 2015 |
| 1       | 1B           | 04/01/2015      | 10/01/2015    | JAN   | 2015 |
| 2       | 2            | 11/01/2015      | 17/01/2015    | JAN   | 2015 |
| 3       | 3            | 18/01/2015      | 24/01/2015    | JAN   | 2015 |
| ..      | ..           | ..              | ..            | ..    | ..   |
| 51      | 51           | 14/12/2014      | 20/12/2015    | DEC   | 2014 |
+---------+--------------+-----------------+---------------+-------+------+

Когда я запускаю следующий оператор:

SELECT * 
FROM   loy_period 
WHERE  To_date('15/04/2015', 'DD/MM/YYYY') BETWEEN 
       To_date(week_start_date, 'DD/MM/YYYY') AND 
       To_date(week_end_date, 'DD/MM/YYYY');

а также

SELECT * 
FROM   loy_period 
WHERE  To_date('15/04/2015', 'DD/MM/YYYY') BETWEEN 
       week_start_date AND 
       week_end_date;

Он возвращает следующее:

+---------+--------------+-----------------+---------------+-------+------+
| week_no | long_week_no | week_start_date | week_end_date | month | year |
+---------+--------------+-----------------+---------------+-------+------+
|      51 | 51           | 14/12/2014      | 20/12/2015    | DEC   | 2014 |
|       1 | 1A           | 01/01/2015      | 03/01/2015    | JAN   | 2015 |
+---------+--------------+-----------------+---------------+-------+------+

И когда я запускаю его с любой датой, он возвращает правильный период в дополнение к записи week_no 51!

Я в замешательстве, почему это происходит. Оба столбца week_start_date и week_end_date типа date.

Fiddle работает должным образом.


sql oracle date-arithmetic oracle11g to-date
person Hawk    schedule 10.04.2015    source источник
comment
Если они уже относятся к типу date, с какой стати вы называете их To_Date?   -  person Damien_The_Unbeliever    schedule 10.04.2015
comment
И действительно: TO_DATE: не использовать функция TO_DATE со значением DATE для аргумента char   -  person Damien_The_Unbeliever    schedule 10.04.2015
comment
@Damien_The_Unbeliever Я запускал без To_Date, и он возвращает тот же результат.   -  person Hawk    schedule 10.04.2015
comment
Никогда не используйте TO_DATE в столбце DATE. Он неявно преобразует его в строку, а затем вернет в актуальное состояние, используя формат NLS для конкретной локали.   -  person Lalit Kumar B    schedule 10.04.2015
comment
Можете ли вы поместить свои образцы данных и нормальную версию запроса в sqlfiddle?   -  person Damien_The_Unbeliever    schedule 10.04.2015
comment
Вы уверены, что год указан правильно в строке 51?   -  person Gordon Linoff    schedule 10.04.2015
comment
@GordonLinoff Нет, сейчас 2014 год. Извините, что пропустил это. Я обновил его.   -  person Hawk    schedule 10.04.2015
comment
@Damien_The_Unbeliever Я добавил sqlfiddle.com/#!4/b5bc9/1   -  person Hawk    schedule 10.04.2015
comment
Вам нужно работать над этой скрипкой до тех пор, пока она а) не покажет проблему, с которой вы обращаетесь за помощью, или б) вы сами не обнаружите проблему.   -  person Damien_The_Unbeliever    schedule 10.04.2015
comment
Я всегда удивляюсь, почему так много людей думают, что им нужно использовать to_date() для преобразования date в date.   -  person a_horse_with_no_name    schedule 10.04.2015
comment
@GordonLinoff Спасибо за подсказку. Я не ожидал, что это может быть неправильная вставка в таблицу. Теперь я понимаю результат.   -  person Hawk    schedule 10.04.2015
comment
@a_horse_with_no_name Честно говоря, я использовал его, думая, что мне нужно принудительно установить какой-то формат даты. Я точно не помню, что я где-то читал, что формат даты, хранящийся в БД, отличается от того, который мы видим в таблицах (который соответствует формату сеанса). Может быть, это не имеет значения. Но шанс для меня узнать   -  person Hawk    schedule 10.04.2015
comment
@hawk, нет проблем. Сегодня ты узнал кое-что хорошее. Прочитайте этот хороший пост в блоге Эда Стивенса на дату edstevensdba.wordpress.com/2011/04. /07/nls_date_format   -  person Lalit Kumar B    schedule 10.04.2015
comment
Даты не имеют формата. Любой формат, который вы видите, применяется приложением, отображающим данные   -  person a_horse_with_no_name    schedule 10.04.2015
comment
@LalitKumarB Я внимательно прочитаю.   -  person Hawk    schedule 10.04.2015

Ответы (1)


arrow_upward
0
arrow_downward

У меня это прекрасно работает при правильном использовании значений TO_DATE и DATE.

  • Никогда не используйте TO_DATE в DATE, это неявно преобразует его в строку, а затем вернет дату, используя формат NLS для конкретной локали.

  • '01/01/2015' — это НЕ ДАТА, это STRING. Вы должны использовать TO_DATE, чтобы явно преобразовать его в DATE.

Посмотрите, что происходит:

SQL> explain plan for select * from dual where to_date(sysdate) > to_date(sysdate -1);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(TO_DATE(TO_CHAR(SYSDATE@!))>TO_DATE(TO_CHAR(SYSDATE@!-1)))

14 rows selected.

SQL>

Таким образом, фактически применяется фильтр filter(TO_DATE(TO_CHAR(SYSDATE@!)). Вы получите неправильный вывод из-за неявного преобразования на основе формата NLS для конкретной локали.

Во всяком случае, теперь возвращаясь к вашему первоначальному вопросу.

Например,

Допустим, ваши данные выглядят так:

Настройка:

SQL> CREATE TABLE t
  2      (week_no VARCHAR2(2), long_week_no VARCHAR2(2), week_start_date DATE, week_end_date DATE, mnth VARCHAR2(3), yr VARCHAR2(4))
  3  ;

Table created.

SQL>
SQL>
SQL> INSERT ALL
  2      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  3           VALUES ('1', '1A', TO_DATE('01/01/2015','DD/MM/YYYY'), TO_DATE('03/01/2015','DD/MM/YYYY'), 'JAN', '2015')
  4      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  5           VALUES ('1', '1B', TO_DATE('04/01/2015','DD/MM/YYYY'), TO_DATE('10/01/2015','DD/MM/YYYY'), 'JAN', '2015')
  6      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  7           VALUES ('2', '2', TO_DATE('11/01/2015','DD/MM/YYYY'), TO_DATE('17/01/2015','DD/MM/YYYY'), 'JAN', '2015')
  8      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
  9           VALUES ('3', '3', TO_DATE('18/01/2015','DD/MM/YYYY'), TO_DATE('24/01/2015','DD/MM/YYYY'), 'JAN', '2015')
 10      INTO t (week_no, long_week_no, week_start_date, week_end_date, mnth, yr)
 11           VALUES ('51', '51', TO_DATE('20/12/2014','DD/MM/YYYY'), TO_DATE('26/12/2015','DD/MM/YYYY'), 'DEC', '2014')
 12  SELECT * FROM dual
 13  ;

5 rows created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>

Таблица:

SQL> SELECT * FROM t;

WE LO WEEK_STAR WEEK_END_ MNT YR
-- -- --------- --------- --- ----
1  1A 01-JAN-15 03-JAN-15 JAN 2015
1  1B 04-JAN-15 10-JAN-15 JAN 2015
2  2  11-JAN-15 17-JAN-15 JAN 2015
3  3  18-JAN-15 24-JAN-15 JAN 2015
51 51 20-DEC-14 26-DEC-15 DEC 2014

SQL>

Запрос для фильтрации строк по диапазону DATE:

SQL> SELECT *
  2  FROM   t
  3  WHERE  To_date('15/01/2015', 'DD/MM/YYYY') BETWEEN
  4         week_start_date AND
  5         week_end_date;

WE LO WEEK_STAR WEEK_END_ MNT YR
-- -- --------- --------- --- ----
2  2  11-JAN-15 17-JAN-15 JAN 2015
51 51 20-DEC-14 26-DEC-15 DEC 2014

SQL>
person Lalit Kumar B    schedule 10.04.2015