My TO_DATE seeming not to function properly

Question

So I am trying to get the section id and the amount of students in that section who enrolled on 02/10/2007. The query returns no results when it should return 6 rows.

The date format its in already is DD-MON-YY.

This is what I have so far: I took the TO_DATE from another query I did and it worked properly on. The query works without it so im sure its somthing to do with the TO_DATE

SELECT section_id, COUNT(student_id) "ENROLLED"
FROM enrollment
WHERE enroll_date = TO_DATE('2/10/2007', 'MM/DD/YYYY')
GROUP BY section_id
ORDER BY ENROLLED;

Answer 1

Most probably the issue is that there is a fractional date component that you are not taking into account. You can ignore that fractional date component by truncating the column in your query:

SELECT section_id, COUNT(student_id) "ENROLLED"
FROM enrollment
WHERE TRUNC(enroll_date) = TO_DATE('2/10/2007', 'MM/DD/YYYY')
GROUP BY section_id
ORDER BY ENROLLED;

I am assuming that the column enroll_date is of the data type DATE.

Some explanation: Oracle stores dates as described here, it does NOT store a date as you state "The date format its in already is DD-MON-YY.". That is only the format you see the date in, which is determined by the parameter NLS_DATE_FORMAT for your session.

Lets do a quick test with a test table. Create table and check the NLS_DATE_FORMAT form my session.

create table DATE_TST 
( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  test_date DATE
);

INSERT INTO date_tst (test_date) VALUES (SYSDATE);

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT';

DD-MON-YYYY

This is how I will see my dates.

SELECT * FROM date_tst;

04-OCT-2020

So I have todays date. Cool. Now lets see if I can query using that date:

SELECT * FROM date_tst WHERE test_date = TO_DATE('04-OCT-2020','DD-MON-YYYY');

no rows.

No rows are shown because the date format I get my date in does not have a time component. DATE has Year, month, day, hour, minute and seconds. The format only has year, month and day. Lets query the data to check if there is a time component.

SELECT TO_CHAR(test_date,'DD-MON-YYYY HH24:MI:SS') FROM date_tst;

4-OCT-2020 21:12:39

Ah there it is... SYSDATE is the current time up to the second. Now lets try that query again with a more precise date format:

SELECT * FROM date_tst WHERE test_date = TO_DATE('04-OCT-2020 21:12:39','DD-MON-YYYY HH24:MI:SS');

04-OCT-2020

And there is our row. The TRUNC command will cut off the time component:

SELECT TO_CHAR(TRUNC(test_date),'DD-MON-YYYY HH24:MI:SS') FROM date_tst;

04-OCT-2020 00:00:00

So you can simplify your query:

SELECT * FROM date_tst WHERE TRUNC(test_date) = TO_DATE('04-OCT-2020','DD-MON-YYYY');

04-OCT-2020

Answer 2

TO_DATE('2/10/2007', 'MM/DD/YYYY') gives you a date at midnight; however,this will only match values at that instant. What you need to do is either:

• TRUNCate the dates in your column back to midnight so that your value matches (however, this will prevent you using an index on the column and you would need to use a function-based index); or
• A better solution is to use a date range starting at midnight of the day you want to match and going up-to, but not including, midnight of the next day.

You can do this using TO_DATE or using a date literal:

SELECT section_id,
       COUNT(student_id) "ENROLLED"
FROM   enrollment
WHERE  enroll_date >= DATE '2007-02-10'
AND    enroll_date <  DATE '2007-02-11'
GROUP BY section_id
ORDER BY ENROLLED;

---

As an aside:

The date format its in already is DD-MON-YY.

Assuming that the enroll_date column has a DATE data type then this has no format; it is a binary data type consisting of 7 bytes (for century, year-of-century, month, day, hour, minute and second).

What you are seeing is the default date format the user interface applies when it displays the binary date value to the user and you can change it using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';

(or to whatever format you want.)

This does not change the binary data stored in the column.