Re: Search between date ranges
Date: Wed, 30 Jul 2008 05:47:29 -0700 (PDT)
Message-ID: <6998cdca-ba5b-41a7-90ad-0907f7669703@f63g2000hsf.googlegroups.com>
Comments embedded.
On Jul 30, 5:23 am, Chris <christopherc..._at_hotmail.com> wrote:
> 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-
> 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
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 Received on Wed Jul 30 2008 - 07:47:29 CDT