CODEWITHSUNDEEP
sqlsqldatatypesoracle11gr2
Kyle K
Kyle K

Reputation: 53

Issue querying date from oracle.

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

Upvotes: 3

Views: 46

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

If this gets no rows returned:

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

but this does see data:

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

then you have rows which have a time other than midnight. At this point in the century DD-MON-RR and DD-MON-YY are equivalent, and both will see 09 as 2009, so the date part is right. But the first will only find rows where the time is midnight, while the second is stripping the time off via the trunc, meaning the dates on both sides are at midnight, and therefore equal.

And since this also finds data:

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

... then you have rows at midnight on that date. You might also have rows with other times, so checking the count with the trunc version might be useful.

You can check the times you actually have with:

select to_char(q_date, 'YYYY-MM-DD HH24:MI:SS') from table;

If you do want to make sure you catch all times within the day you can use a range:

select * from table where
q_date >= date '2009-03-16'
and q_date < date '2009-03-17';

Quick SQL Fiddle demo.

Although it sounds like you're expecting all the times to be midnight, which might indicate a data problem.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269863

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'

Upvotes: 2

Related Questions