日期比较返回异常结果 - SQL Oracle

2021-12-30 00:00:00 sql oracle11g oracle date-arithmetic to-date

我有一张结构表:

+---------+--------------+-----------------+---------------+-------+------+
| 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 记录！

And when I run it with any date, it will return the correct period in addition to the week_no 51 record!

我很困惑为什么会发生这种情况.week_start_date 和 week_end_date 列都属于 date 类型.

I'm confused why this is happening. Both columns week_start_date and week_end_date of the type date.

Fiddle 按预期工作.

推荐答案

它非常适合我正确使用TO_DATE和DATE值.

It works perfectly for me with proper use of TO_DATE and DATE values.

切勿在 DATE 上使用 TO_DATE，它会将其隐式转换为字符串，然后使用特定于语言环境的 NLS 格式返回日期em>.

Never use TO_DATE on a DATE, It will implicitly convert it into string and then back to date using locale-specific NLS format.

'01/01/2015' 不是日期，而是字符串.您必须使用 TO_DATE 将其显式转换为 DATE.

'01/01/2015' is NOT a DATE, it is a STRING. You must use TO_DATE to explicitly convert it into 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 格式的隐式转换，您将得到不正确的输出.

So, the actual filter applied is filter(TO_DATE(TO_CHAR(SYSDATE@!)) You will get incorrect output due to the implicit conversion based on locale-specific NLS format.

无论如何，现在回到你最初的问题.

Anyway, now coming back to your original question.

例如

假设您的数据如下所示:

Let's say your data looks like:

设置:

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>

相关文章