SQL BETWEEN not working

Question

I have the following statement being run on an oracle database.

SELECT br.Number
  FROM Billing_History br 
 WHERE TRUNC(br.History_Date) BETWEEN to_date('01-Jan-99', 'DD-Mon-YY HH:MI:SS') 
                                  AND to_date('11-May-99', 'DD-Mon-YY HH:MI:SS')

There are definitely records in that table that fall between those dates. And they all have a Number that goes with them, but for some reason this isn't returning any Numbers. It's returning nothing at all.

The dates in the database are in this format '01-Jan-11'. So it seems like I'm putting the dates in the correct format too. Do you see anything wrong with the SQL I wrote?

Answer 1

The problem is not the time component of the format model, it's the 'YY' component, which would mean in your year is converted to 2099, not 1999. Try this to illustrate:

SQL> SELECT to_char(to_date('01-Apr-99','DD-Mon-YY'),'DD-Mon-YYYY') thedate
       FROM dual;

THEDATE
-----------
01-Apr-2099

SQL> 

Either use RR or YYYY as a format model component for year when using 20th century dates.

Edit:

You make the statement "The dates in the database are in this format '01-Jan-11'." This is a common, but incorrect, interpretation of dates in Oracle. DATE fields are always stored in the same internal format. It's all about how you use the format model in conversion functions that dictates how the data is converted to/from internal format.

Answer 2

Use RR in your date format instead of YY. It is probably picking up those dates as 2099 instead of 1999.

SELECT br.Number FROM Billing_History br WHERE  
TRUNC(br.History_Date) BETWEEN to_date('01-Jan-99', 'DD-Mon-RR HH:MI:SS') 
AND to_date('11-May-99', 'DD-Mon-RR HH:MI:SS')