weired behaviour with between operator and sysdate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: weired behaviour with between operator and sysdate

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

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    Everything is working as intended. The situation is that when you insert using SYSDATE, you don't just get 10-MAY-02, you get 10-MAY-2002 07:53:15.90 or whatever the actual date-time is then. When you insert using a literal, you get all zeros for anything you didn't include. Thus when you insert 10-MAY-02, what you really inserted was 10-MAY-2002 00:00:00.00. When you use between, you get everything 10-MAY-2002 00:00:00.00 and earlier. The sysdate is AFTER that date-time, and is thus not included. Want to avoid excluding that row? Use SYSDATE in the between clause also.
    Oracle DBA and Developer

  3. #3
    Join Date
    Apr 2002
    Posts
    14
    Alternatively use TRUNC(SYSDATE) when you want to store todays date (without time - ie. time of 00:00:00 ) so that your insert would use trunc(sysdate).

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