-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|