Re: can I create Date Variable?

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 -- Received on Thu Sep 04 2008 - 13:21:43 CDT