Re: select with between date/time

From: Phany B. LeBlanc <phanyl_at_nbnet.nb.ca>
Date: Fri, 24 Aug 2001 22:37:25 GMT
Message-ID: <FGAh7.717$fb.71252@news-nb00s0.nbnet.nb.ca>

You could try something like this:

---

DECLARE
   date1 VARCHAR2(20);
   date2 VARCHAR2(20);

/* EXPLICIT CURSOR WITH PARAMETERS */
   CURSOR whatever_crsr (lo_date in VARCHAR2, hi_date in VARCHAR2) IS

      SELECT *
      FROM YOUR_TABLE
      WHERE DATESTAMP_COL BETWEEN to_date(lo_date,'MM/DD/YYYY HH24:MI:SS')

AND to_date(hi_date,'MM/DD/YYYY HH24:MI:SS'); BEGIN
/* STEP 1: GET DATE1 [YESTERDAY AT 7PM] */
   SELECT to_char(sysdate-1, 'MM/DD/YYYY') || ' 19:00:00'    INTO date1
   FROM dual;

/* STEP 2: GET DATE2 [TODAY AT 4AM] */
   SELECT to_char(sysdate, 'MM/DD/YYYY') || ' 04:00:00'    INTO date2
   FROM dual;

/* STEP 3: SANITY CHECK */

   DBMS_OUTPUT.PUT_LINE('DATE1 IS: ' || date1);    DBMS_OUTPUT.PUT_LINE('DATE2 IS: ' || date2);

/* STEP 4: OPEN THE CURSOR USING 2 PARAMS [DATE1 AND DATE2]
   OPEN whatever_crsr( date1, date2 );

   .
   .
   .

END;
/

---

Cheers,
Phany Received on Fri Aug 24 2001 - 17:37:25 CDT