sql - Strange Oracle date behavior -

I had a strange problem in one of our production databases. Long story short, simple query:

  Select ID, from triangle (stdate) to table_name where trunc (stdate) = '05 -FEB-09 ';  

No line returned though,

  select from the table trunc (stdate) table_name where id = sought_after_id;  

returned '05 -FEB-09 '. Only after I try:

  Setup table_name set stdate = '05 -FEB-09 'id = sought_after_id;  

My original query works as expected:

  id, select trunc (stdate) from the table_name where trunc (stdate) = '05 -FEB-09 '; & Gt; Demand_uper_id, '05 -FEB-09 ' 

So, what was happening with my standard values?

You should compare dates with dates (apples for apples ...) and false conversion Should not be trusted.

Since TRUNC (date) on a date you should compare it with a date:

  id, trunc (stdate) from table_name Choose where trunc (stdate) = DATE '2009-02-05'  

or

  select id, trunc (stdate) from table_name where trunc (stdate ) In response to the first comment of Igor = TO_DATE ('05 -FEB-09 ',' DD-MON-RR '))  

Update :

Depending on the underlying data conversion, the result of the query depends on many session parameters . One of these parameters should be modified if you are now seeing different results from earlier days. You can not rely on your query "session-independent" to an unbalanced conversion.

It is worth noting that your first query depends on the parameter of client session if the session has its default date display setting NLS_DATE_FORMAT , then it will not return the same result.

On the respective note, there is a perfectly acceptable date format for DD-mon-RR display , but it is not suitable for use in your code. Is because there is ambiguity related to the century and you rely on NLS_DATE_LANGUAGE parameters for months.