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

On Sep 4, 9:01*am, AP <adamwphoe...@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

On Sep 4, 11:05*am, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
> On Sep 4, 9:01*am, AP <adamwphoe...@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 --

On Sep 4, 1:21*pm, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On Sep 4, 11:05*am, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
>
>
>
>
>
> > On Sep 4, 9:01*am, AP <adamwphoe...@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