Re: can I create Date Variable?
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