CODEWITHSUNDEEP
sqloracle-database
Graham
Graham

Reputation: 5884

SQL BETWEEN not working

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?

Upvotes: 5

Views: 4246

Answers (3)

DCookie
DCookie

Reputation: 43523

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.

Upvotes: 11

Datajam
Datajam

Reputation: 4231

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')

Upvotes: 5

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

Try removing the time part from the second to_date parameter:

to_date('11-May-99', 'DD-Mon-YY')

Or even better:

to_date('11-05-1999', 'DD-MM-YYYY')

This is more robust as it is language agnostic and doesn't need to guess the century.

Upvotes: 3

Related Questions