 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Oracle / Oracle Server / July 2008Search between date ranges
Hi Guys, I am trying to bild a cursor based on a select statement which returns values between a certain date range. I have read that it is best avoiding the BETWEEN function of SQL. Instead I hav written : AND ol.actual_shipment_date >= p_date_from AND ol.actual_shipment_date <= (p_date_to) If I have the p_date_from parameter set to 01-JAN-08 and the p_date_to parameter set to 10-JAN-08 it will only return dates from 01-JAN to 09- JAN (not the 10th) To overcome this I have written : AND ol.actual_shipment_date >= p_date_from AND ol.actual_shipment_date <= (p_date_to +1) This seems a very crude way of doing it and was wondering if there is an easier way? Thanks in advance, Chris > Hi Guys, > [quoted text clipped - 21 lines] > Thanks in advance, > Chris I guess you have to TRUNC your dates at both sides (trunc(date1) <= trunc(date2)) Shakespeare > > Hi Guys, > [quoted text clipped - 28 lines] > > - Show quoted text - As Shakespeare suggested you shoud trunc the target_date you are testing >= against and add one to the trunc end_target_date_range for the < (not <=) end. In the past the optimizer would automatically convert a BEWTEEN into >= and <= conditions (transformation). If the optimizer no longer does this then it is now probably unnecessary. If you are looking only for rows affected on a single date I would use the >= and < syntax instead of BETWEEN otherwise you may need to set the ending time to 23:59:59 least you pick up entries with a time one second past what you want. HTH -- Mark D Powell -- Comments embedded. > Hi Guys, > > I am trying to bild a cursor based on a select statement which returns > values between a certain date range. I have read that it is best > avoiding the BETWEEN function of SQL. Where did you read that? It clearly is not true: SQL> select ename, empno, hiredate 2 from emp 3 where hiredate between to_date('17-DEC-1980','DD-MON-YYYY') and to_date('17-NOV-1981','DD-MON-YYYY') 4 / ENAME EMPNO HIREDATE ---------- ---------- --------- SMYTHE 7369 17-DEC-80 ALLEN 7499 20-FEB-81 WARD 7521 22-FEB-81 JONES 7566 02-APR-81 MARTIN 7654 28-SEP-81 BLAKE 7698 01-MAY-81 CLARK 7782 09-JUN-81 KING 7839 17-NOV-81 TURNER 7844 08-SEP-81 9 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 162 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 9 | 162 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HIREDATE"<=TO_DATE('1981-11-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "HIREDATE">=TO_DATE('1980-12-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 490 bytes sent via SQL*Net to client 246 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9 rows processed SQL> SQL> SQL> select ename, empno, hiredate 2 from emp 3 where hiredate >= to_date('17-DEC-1980','DD-MON-YYYY') and hiredate <= to_date('17-NOV-1981','DD-MON-YYYY') 4 SQL> / ENAME EMPNO HIREDATE ---------- ---------- --------- SMYTHE 7369 17-DEC-80 ALLEN 7499 20-FEB-81 WARD 7521 22-FEB-81 JONES 7566 02-APR-81 MARTIN 7654 28-SEP-81 BLAKE 7698 01-MAY-81 CLARK 7782 09-JUN-81 KING 7839 17-NOV-81 TURNER 7844 08-SEP-81 9 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 162 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 9 | 162 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HIREDATE"<=TO_DATE('1981-11-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "HIREDATE">=TO_DATE('1980-12-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 490 bytes sent via SQL*Net to client 246 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 9 rows processed SQL> Note both queries produce the same execution plan, with the same filter, as BETWEEN is silently rewritten to the form you've implemented. > Instead I hav written : > > AND ol.actual_shipment_date >= p_date_from > AND ol.actual_shipment_date <= (p_date_to) Which, as shown above, Oracle does for you. > If I have the p_date_from parameter set to 01-JAN-08 and the p_date_to > parameter set to 10-JAN-08 it will only return dates from 01-JAN to 09- [quoted text clipped - 10 lines] > Thanks in advance, > Chris A solution has already been presented, but I will repeat it here: AND trunc(ol.actual_shipment_date) >= p_date_from AND trunc(ol.actual_shipment_date) <= p_date_to Of course it appears you're relying upon a default date mask, which can backfire on you without warning since the default used in one installation may not be the same as the chosen 'default' for another: SQL> select empno, ename, hiredate 2 from emp 3 where hiredate <= '17-NOV-81' 4 / EMPNO ENAME HIREDATE ---------- ---------- --------- 7369 SMYTHE 17-DEC-80 7499 ALLEN 20-FEB-81 7521 WARD 22-FEB-81 7566 JONES 02-APR-81 7654 MARTIN 28-SEP-81 7698 BLAKE 01-MAY-81 7782 CLARK 09-JUN-81 7839 KING 17-NOV-81 7844 TURNER 08-SEP-81 9 rows selected. SQL> SQL> alter session set nls_date_format = 'MON-DD-YYYY'; Session altered. SQL> SQL> select empno, ename, hiredate 2 from emp 3 where hiredate <= '17-NOV-81' 4 / where hiredate <= '17-NOV-81' * ERROR at line 3: ORA-01843: not a valid month SQL> As a better solution you should use either the to_date function or the to_char function; I would opt for the latter in this case: AND to_char(ol.actual_shipment_date, 'DD-MON-RR') BETWEEN p_date_from and p_date_to This would return the values you expect, and eliminates your kludge. David Fitzjarrell >Comments embedded. >(..............) [quoted text clipped - 4 lines] > >David Fitzjarrell Yes but silently rewriting a query takes a lot of time... ;-) Shakespeare > "fitzjarr...@cox.net" <orat...@msn.com> schreef in berichtnews:6998cdca-ba5b-41a7-90ad-0907f7669703@f63g2000hsf.googlegroups.com... > [quoted text clipped - 10 lines] > > Shakespeare Yes, I suppose microseconds could be considered a great amount of time ... to an amoeba ... David Fitzjarrell On 30 Jul., 14:47, "fitzjarr...@cox.net" <orat...@msn.com> wrote: Comments embedded. > > Hi Guys, > [quoted text clipped - 3 lines] > > Where did you read that? It clearly is not true: ... > Note both queries produce the same execution plan, with the same > filter, as BETWEEN is silently rewritten to the form you've > implemented. full ack > A solution has already been presented, but I will repeat it here: > > AND trunc(ol.actual_shipment_date) >= p_date_from > AND trunc(ol.actual_shipment_date) <= p_date_to Bad! This way the database can't use an index on ol.actual_shipment_date. Instead, use AND ol.actual_shipment_date >= p_date_from AND ol.actual_shipment_date < p_date_to + 1 this will include records from p_date_from, 0:00:00 through p_date_to, 23:59:59 > Of course it appears you're relying upon a default date mask, I can't see why. He is building a cursor, so maybe he uses PL/SQL, and p_date_from and p_date_to might well be true DATEs. Urs Metzger > On 30 Jul., 14:47, "fitzjarr...@cox.net" <orat...@msn.com> wrote: > Comments embedded. [quoted text clipped - 35 lines] > > Urs Metzger Only if p_date_to is a trunc-ed date (hh:mi = 00:00), but you're right. So to the OP: Yes, the crude way seems to be the best way .... BTW: I think a between would have required the +1 too...? Shakespeare > > On 30 Jul., 14:47, "fitzjarr...@cox.net" <orat...@msn.com> wrote: > > Comments embedded. [quoted text clipped - 39 lines] > So to the OP: Yes, the crude way seems to be the best way .... > BTW: I think a between would have required the +1 too...? Yes, but you need to subtract a second from the to-date: AND ol.actual_shipment_date BETWEEN trunc(p_date_from) AND trunc(p_date_to) + 1 - 1/24/60/60 Urs Metzger |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.