Re: how to get the date range

On 21 okt, 09:29, muhammaddzulkarn..._at_gmail.com wrote:
> 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?

Listen to Jim.
Also refer to the add_months function in the SQL reference manual. add_monts(<date>, <number of months>) where a negative value refers to previous months

--
Sybrand Bakker
Senior Oracle DBA

99.9 percent of questions on various Oracle fora has already been
answered by the one thing no one reads:
The online documentation!