how to get the date range

Hello Everyone

I have a problem to define the date range. Firstly, this is my sql statement:

define date_start = '20080801';
define date_end = '20080901';

select hostname, to_char(to_date(&date_start, 'YYYYMMDD') , 'YYYY'), to_char(to_date(&date_start, 'YYYYMMDD') , 'MM'), genesis_port, instance_name, api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count) from gen_api_performance_daily
where to_char(statistics_date,'YYYYMMDD') between &date_start and &date_end
group by hostname, to_char(to_date(&date_start, 'YYYYMMDD') , 'YYYY'), to_char(to_date(&date_start, 'YYYYMMDD') , 'MM'), genesis_port, instance_name, api_name;

and this is my table gen_api_performance_daily:

 SQL> desc gen_api_performance_daily

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 HOSTNAME                                  NOT NULL VARCHAR2(32)
 STATISTICS_DATE                           NOT NULL DATE
 GENESIS_PORT                              NOT NULL NUMBER
 INSTANCE_NAME                             NOT NULL VARCHAR2(64)
 API_NAME                                  NOT NULL VARCHAR2(128)
 TOTAL_TIME                                         NUMBER
 MAX_TIME                                           NUMBER
 MIN_TIME                                           NUMBER
 AVERAGE_TIME                                       NUMBER
 COUNT                                              NUMBER

i have defined date_start as 20080801 and date_end as 20080901. So that is mean the statistics_date month is equal to the previous month. My problem is however, how will i get the date_start and date_end values? In my sql samples, they are still hardcoded. I cannot hardcode the values for date_start and date_end it in the script. The script should be able to know how to get the date range for the previous month, or the previous year. Any ideas? Received on Tue Oct 21 2008 - 02:29:47 CDT