Re: can I create Date Variable?
Date: Fri, 5 Sep 2008 10:46:17 -0700 (PDT)
Message-ID: <bf499c81-0fd3-4f86-90e3-5e9109ee4c64@l42g2000hsc.googlegroups.com>
On Sep 4, 1:21 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Sep 4, 11:05 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> If you are running the query from SQLPLus you might just be able to
> use a SQLPlus substitution variable.
>
> select .... from ... where date_col >= to_date('&the_date','YYYMMDD')
>
> You will be prompted for the value of &the_date by SQLPlus. Use && if
> you need to use the variable in the query several times
>
> See the SQLPlus manual and look up the ACCEPT, PROMPT, and UNDEFINE
> commands as well as look for substitiution (label) variables to
> provide more control over the variable input and reuse.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
To follow up with this using such replacement variables is easy:
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and
to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '02/01/81'
Enter value for enddt: '02/28/81'
old 3: where hiredate between to_date(&startdt, 'MM/DD/RR') and
to_date(&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('02/01/81', 'MM/DD/RR') and
to_date('02/28/81', 'MM/DD/RR')
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> Note the single & maintains the value for the duration of the query. Using && allows you to pass the value through the script to several queries:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/83'
Enter value for enddt: '01/31/83'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL> which can 'backfire' if you're not careful (like it did above). To fix that it's necessary to undefine the variables:
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
4 /
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and
to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/83', 'MM/DD/RR') and
to_date('01/31/83', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 12-JAN-83
1100 20
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL>
SQL> select *
2 from emp
3 where hiredate between to_date(&&startdt, 'MM/DD/RR') and to_date(&&enddt, 'MM/DD/RR')
4 /
Enter value for startdt: '01/01/82'
Enter value for enddt: '01/31/82'
old 3: where hiredate between to_date(&&startdt, 'MM/DD/RR') and to_date(&&enddt, 'MM/DD/RR')
new 3: where hiredate between to_date('01/01/82', 'MM/DD/RR') and to_date('01/31/82', 'MM/DD/RR')
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO BONUS
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82
1300 10
SQL>
SQL> undefine startdt
SQL> undefine enddt
SQL> Once undefined the variables are ready for new assignments. You can do this with any name you like (even numerics) but it's difficult for others to know what, say, &&1 mighr be used to do:
Enter value for 1:
Not very descriptive, which is why one should use more definitive names for such variables (as illustrated).
David Fitzjarrell Received on Fri Sep 05 2008 - 12:46:17 CDT