Geek Answers Handbook

SQL BETWEEN not working

I have the following statement executed in an oracle database. A.

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 certain entries in this table that are between these dates. And everyone has a number that goes with them, but for some reason this does not return any numbers. It does not return anything.

Dates in the database are in this format '01 -Jan-11 '. So it seems that I am giving dates in the correct format. Do you see something wrong with the SQL I wrote?

+5
source share
3 answers

The problem is not the temporary component of the format model, but the "YY" component, which will mean in your year, will be 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>

RR YYYY 20- .

Edit:

" '01 -Jan-11 '." , Oracle. DATE . , , , / .

+11

RR YY. , 2099 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')
+5

to_date:

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

:

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

, .

+3

More articles:


All Articles