DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL problem

  1. #1
    Join Date
    Dec 2000
    Posts
    37
    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.

  2. #2
    Join Date
    Jan 2001
    Posts
    28
    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.
    - Mayur.

  3. #3
    Join Date
    Dec 2000
    Posts
    28
    Try using parenthesis to enforce the order of execution.

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


    Hope this helps.

    AD


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width