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
> /
>

Shawn,

The following example worked for me and follows your current method:

select to_char(to_date('15'||substr(trunc(sysdate-10,'MM'),3,9)),'dd-MON-yyyy') middate1 from dual
/

select to_char(to_date('16'||substr(trunc(sysdate-10,'MM'),3,9)),'dd-MON-yyyy') middate2 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 '&&middate1' group by trunc(ACTY_DATE_TIME)
/

select to_char(trunc(ACTY_DATE_TIME),'DD-MON-YYYY') "date", count(1) "count" from KEYLINK_ACTY
where trunc(ACTY_DATE_TIME) between '&&middate2' and '&&enddate' group by trunc(ACTY_DATE_TIME)
/

Regards,
Paul KE Walker Received on Tue Feb 12 2002 - 20:31:50 CET