Hi,

I have observed a weired problem with between operator and sysdate.

please observe the following queries

/*I have inserted a row as below with the date as '9-MAY-02'
*/
SQL> insert into test values (8885,'9-MAY-02');

1 row created.

/* Then i have run the following query. which gives the results from 20-apr-02 to 10-may-02 (including 10-may-02). This is fine. But observe the follwing two queries also.
*/
SQL> select empno,hiredate from test where hiredate between '20-apr-02' and '10-MAY-02';

EMPNO HIREDATE
---------- ---------
8881 20-APR-02
8882 23-APR-02
8883 03-MAY-02
8884 06-MAY-02
8885 09-MAY-02

5 rows selected.

/* Here I have inserted a row with date as sysdate.
*/

SQL> insert into test values (8886,sysdate);

1 row created.

/* Now I have run the following query, but it has return the rows from 20-apr-02 to 9-may-02 only. The actual behaviour is, it has to return the rows even with 10-may-02.
*/
SQL> select empno,hiredate from test where hiredate between '20-apr-02' and '10-MAY-02';

EMPNO HIREDATE
---------- ---------
8881 20-APR-02
8882 23-APR-02
8883 03-MAY-02
8884 06-MAY-02
8885 09-MAY-02

5 rows selected.

SQL> select empno,hiredate from test where hiredate between '20-apr-02' and '11-MAY-02';

EMPNO HIREDATE
---------- ---------
8881 20-APR-02
8882 23-APR-02
8883 03-MAY-02
8884 06-MAY-02
8885 09-MAY-02
8886 10-MAY-02

6 rows selected.


what I have observed is, when we insert the rows with sysdate instead of actual date format, this problem is rising.

In my application, I have used both date format (10-may-02) and sysdate. Why its behaving like that? And How I have to resolve this problem?

Thank's for your help

Srinivas Medukonduru