Issue querying date from oracle.

Question

I understand that querying a date will fail as its comparing a string to date and that can cause an issue.

Oracle 11.2 G Unicode DB NLS_DATE_FORMAT DD-MON-RR

select * from table where Q_date='16-Mar-09'; 

It can be solved by

select * from table where trunc(Q_date) = TO_DATE('16-MAR-09', 'DD-MON-YY');

What I don't get is why this works.

select* from table where Q_date='07-JAN-08';

If anyone can please elaborate or correct my mindset. Thanks

Answer 1

Oracle does allow date literals, but they depend on the installation (particularly the value of NLS_DATE_FORMAT as explained here). Hence, there is not a universal format for interpreting a single string as a date (unless you use the DATE keyword).

The default format is DD-MM-YY, which seems to be the format for your server. So, your statement:

where Q_date = '07-JAN-08'

is interpreted using this format.

I prefer to use the DATE keyword with the ISO standard YYYY-MM-DD format:

where Q_Date = DATE '2008-01-07'