Re: can I create Date Variable?

From: <fitzjarrell_at_cox.net>
Date: Thu, 4 Sep 2008 08:05:11 -0700 (PDT)
Message-ID: <c1a01224-8408-49e0-b5b2-3f62b2db6a33@x16g2000prn.googlegroups.com>


On Sep 4, 9:01 am, AP <adamwphoe..._at_gmail.com> wrote:
> I have a union query that combines the results of several queries.
> Each of these queries has a condition to extract the row where the
> date is between 1/1/08 and 1/31/08. Each month when I run this I have
> to do a find and replace to change the date range. Is there a way to
> declare a date variable and reference that in the select statement
> instead of the literal date?
>
> For example
> instead of between 1/1/08 and 1/31/08
>
> it would be something like...
>
> startDate = 1/1/08
> endDate = 1/31/08
>
> statement = between startdate and enddate
>
> Thanks in advance

Why are you relying upon default formats for your date strings? This is the surest way to write code that can, and will, fail should that default be changed.

Now, to answer your question, yes, you can declare variables, use them, and reassign values to them:

SQL> variable startdt varchar2(8)
SQL> variable enddt varchar2(8)
SQL>
SQL> exec :startdt := '01/01/82';

PL/SQL procedure successfully completed.

SQL> exec :enddt := '01/31/82';

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2 from emp
  3 where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR')
  4 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO      BONUS

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-82
1300                    10

SQL>
SQL> exec :startdt := '02/01/81';

PL/SQL procedure successfully completed.

SQL> exec :enddt := '02/28/81';

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2 from emp
  3 where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR')
  4 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO      BONUS

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250
500         30

SQL>
SQL> exec :startdt := '01/01/83';

PL/SQL procedure successfully completed.

SQL> exec :enddt := '01/31/83';

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2 from emp
  3 where hiredate between to_date(:startdt, 'MM/DD/RR') and to_date(:enddt, 'MM/DD/RR')
  4 /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO      BONUS

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83
1100                    20

SQL> David Fitzjarrell Received on Thu Sep 04 2008 - 10:05:11 CDT

Original text of this message