Re: dates

"Shawn Edwards" <spedwards_at_qwest.net> wrote in message news:<8sZ88.172$6I1.90395_at_news.uswest.net>...
> Hi,
>
> I have been running this report at the end of every month for the previous
> month (script below). Well, my supervisors got picky and asked that it be
> run twice a month. Run it on the 15th for the 1st half of the month
> (1st-15th) then a 2nd time at the end of each month for the 2nd half of the
> month (16th - 30th or 31st).
> I was so happy when I got my original script to run without me entering the
> dates each time. But, I can't figure out the new way. Is it possible?
>
> select to_char(trunc(sysdate-10,'MM'),'dd-MON-yyyy') begdate
> from dual
> /
>
> select to_char(last_day(trunc(sysdate-10,'MM')),'dd-MON-yyyy') enddate
> from dual
> /
>
> select to_char(trunc(ACTY_DATE_TIME),'DD-MON-YYYY') "date", count(1) "count"
> from KEYLINK_ACTY
> where trunc(ACTY_DATE_TIME) between '&&begdate' and '&&enddate'
> group by trunc(ACTY_DATE_TIME)
> /
>
> Thanks,
> Shawn

You need to 'save' the results in defines You can do that by using the new_value clause of the column command so

column begdate new_value bdate
column enddate new_value edate

> select to_char(trunc(sysdate-10,'MM'),'dd-MON-yyyy') begdate
> from dual
> /
>
> select to_char(last_day(trunc(sysdate-10,'MM')),'dd-MON-yyyy') enddate
> from dual
> /
>

select ..
from
where ... between to_date('&bdate') and to_date('&edate')

Hth

Sybrand Bakker
Senior Oracle DBA Received on Mon Feb 11 2002 - 09:25:04 CET