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!