SQL problem

[daphnny]

Hi,

I have the following two sql statements:

1. select empno from emp where employed_date < sysdate -60 order by employed_date;

2. select empno from emp where employed_date < sysdate -60;

When I run statement 1, I get the right results, which give me all empno hired 2 months ago.
But statement number 2 returns record of empno hired one month ago. Seems like the where clause does not work properly here.

Any help will be greatly appreciated.

[tmnath]

Hi,
I tried working with the same scenario, but am not able to regenerate the problem you are having. The following is the data that I have

ID DT
--------- ---------
1 17-JAN-01
2 07-JAN-01
3 18-DEC-00
4 28-NOV-00
5 18-NOV-00
6 08-NOV-00
7 29-OCT-00

and when i run the queries i get the following

SQL> select id from emp where dt < sysdate-60;
Press Enter to Continue...
ID
---------
5
6
7

SQL> select id from emp where dt<sysdate-60 order by dt;
Press Enter to Continue...
ID
---------
7
6
5

If you are still getting the problem and the issue is with dates at the border, try using the TRUNC function on the date so that the time part is not checked.
If it still doesn't work, lemme know and gimme a few test cases that ain't working at your end.

[ad]

Try using parenthesis to enforce the order of execution.

select empno
from employee
where employed_date < ( sysdate - 60 );


Hope this helps.

AD