How to compare date in Oracle?

Question

I'm having issues with what I assumed would be a simple problem, but googling isn't helping a great load. Possibly I'm bad at what I am searching for nether the less.

SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';

It tells me I am using an invalid identifier. I have tried using MON instead of MMM, but that doesn't help either.

Unsure if it makes any difference but I am using Oracle SQL Developer.

Answer 1

There are multiple issues related to your DATE usage:

WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';

1. FORMAT is not an Oracle supported built-in function.
2. Never ever compare a STRING with DATE. You might just be lucky, however, you force Oracle to do an implicit data type conversion based on your locale-specific NLS settings. You must avoid it. Always use TO_DATE to explicitly convert string to date.

WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');

3. When you are dealing only with date without the time portion, then better use the ANSI DATE Literal.

WHERE ORDER_DATE = DATE '2000-06-07';

Read more about DateTime literals in documentation.

---

Update

It think it would be helpful to add some more information about DATE.

Oracle does not store dates in the format you see. It stores it internally in a proprietary format in 7 bytes with each byte storing different components of the datetime value.

BYTE         Meaning
----         -------
1 Century -- stored in excess-100 notation
2 Year    --       "                "
3 Month   -- stored in 0 base notation
4 Day     --    "                   "
5 Hour    -- stored in excess-1 notation
6 Minute  --    "                   "
7 Second  --    "                   "

Remember,

To display                      : Use TO_CHAR
Any date arithmetic/comparison  : Use TO_DATE

Performance Bottleneck:

Let's say you have a regular B-Tree index on a date column. now, the following filter predicate will never use the index due to TO_CHAR function:

WHERE TO_CHAR(ORDER_DATE, 'DD-MM-YYYY') = '07-06-2000';

So, the use of TO_CHAR in above query is completely meaningless as it does not compare dates, nor does it delivers good performance.

Correct method:

The correct way to do the date comparison is:

WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');

It will use the index on the ORDER_DATE column, so it will much better in terms of performance. Also, it is comparing dates and not strings.

As I already said, when you do not have the time element in your date, then you could use ANSI date literal which is NLS independent and also less to code.

WHERE ORDER_DATE = DATE '2000-06-07';

It uses a fixed format 'YYYY-MM-DD'.