Date comparison returns unusual result - SQL Oracle

Question

I have a table of the structure:

+---------+--------------+-----------------+---------------+-------+------+
| 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 |
+---------+--------------+-----------------+---------------+-------+------+

When I run the following statement:

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');

and

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

It returns the following:

+---------+--------------+-----------------+---------------+-------+------+
| 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 |
+---------+--------------+-----------------+---------------+-------+------+

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

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

Fiddle works as expected.

If they're already of type `date`, why on earth are you calling `To_Date` on them? — Damien_The_Unbeliever, Apr 10 '15 at 10:19
And, in fact: [`TO_DATE`](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm): "Do not use the TO_DATE function with a DATE value for the char argument" — Damien_The_Unbeliever, Apr 10 '15 at 10:21
@Damien_The_Unbeliever I ran without `To_Date` and it returns the same result. — Hawk, Apr 10 '15 at 10:23
Never use TO_DATE on a DATE column. It will implicitly convert it into string and then back to date using locale-specific NLS format. — Lalit Kumar B, Apr 10 '15 at 10:24
Can you put your sample data and a sane version of your query into [sqlfiddle](http://sqlfiddle.com)? — Damien_The_Unbeliever, Apr 10 '15 at 10:25
@GordonLinoff No, the year is 2014. Sorry for missing that out. I updated it. — Hawk, Apr 10 '15 at 10:43
@Damien_The_Unbeliever I've added http://sqlfiddle.com/#!4/b5bc9/1 — Hawk, Apr 10 '15 at 10:45
You need to work on that fiddle until it a) exhibits the *problem* you're asking for help with or b) You spot the issue yourself. — Damien_The_Unbeliever, Apr 10 '15 at 10:50
I always wonder why so many people think they need to use `to_date()` to convert a `date` into a `date`. — , Apr 10 '15 at 10:58
@GordonLinoff Thanks for the hint. I did not expect it could be wrong insert into the table. I now understand the result. — Hawk, Apr 10 '15 at 11:01
@a_horse_with_no_name I'll be honest, I used it thinking I need to force some date format. I do not remember exactly what I read somewhere that date format stored in the DB is different than the one we see in the tables (which follows the session format). Maybe this is irrelevant. But chance for me to learn — Hawk, Apr 10 '15 at 11:04
@hawk, no problem. You learned something good today. Read this good blog post by Ed Stevens on Date https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ — Lalit Kumar B, Apr 10 '15 at 11:07
Dates don't have a format. Any format you see is applied by the application displaying the data — , Apr 10 '15 at 11:12

Lalit Kumar B · Accepted Answer · 2015-04-10T11:00:33.737

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

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' is NOT a DATE, it is a STRING. You must use TO_DATE to explicitly convert it into DATE.

See what happens:

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>

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.

For example,

Let's say your data looks like:

Setup:

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>

Table:

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>

Query to filter the rows based on DATE range:

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>

Date comparison returns unusual result - SQL Oracle

1 Answers1

Linked