Enter request date from oracle.
I understand that the date query will fail as it is comparing the string to the date and may cause the problem.
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 with
select * from table where trunc(Q_date) = TO_DATE('16-MAR-09', 'DD-MON-YY');
I don't understand why this works.
select* from table where Q_date='07-JAN-08';
If someone can improve or correct my thinking. Thanks to
source to share
Oracle allows date literals, but they depend on the setting (especially the value NLS_DATE_FORMAT
as described here ). Hence, there is no universal format for interpreting a single string as a date (unless you use a keyword DATE
).
The default format is DD-MM-YY, which appears to be the format for your server. So your statement:
where Q_date = '07-JAN-08'
interpreted using this format.
I prefer to use DATE
the ISO YYYY-MM-DD keyword :
where Q_Date = DATE '2008-01-07'
source to share
If this returns no strings:
select * from table where Q_date='16-Mar-09';
but this is seen by the data:
select * from table where trunc(Q_date) = TO_DATE('16-MAR-09', 'DD-MON-YY');
then you have lines that have a time other than midnight. At this point in this year, DD-MON-RR and DD-MON-YY are equivalent, and both will see 09 in 2009, so the date part is correct. But first of all the lines will be found where the time is midnight, and the second is the trip after trunc
, which means that the dates on both sides are at midnight and therefore equal.
And since this also finds the data:
select* from table where Q_date='07-JAN-08';
... then you have lines at midnight on that date. You may have lines with other points as well, so checking the count with the trunc version might be helpful.
You can check the time you have:
select to_char(q_date, 'YYYY-MM-DD HH24:MI:SS') from table;
If you want you to fish all the time during the day, you can use the range:
select * from table where
q_date >= date '2009-03-16'
and q_date < date '2009-03-17';
Though it seems like you expect it to be midnight all the time, which could indicate a data problem.
source to share