Re: how to get the date range

<muhammaddzulkarnain_at_gmail.com> a écrit dans le message de news: 0c6950cc-7197-4e38-9227-15514261e5eb_at_q9g2000hsb.googlegroups.com...
| 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?
|
|

See answers to your question at:
http://www.orafaq.com/forum/t/126566/102589/

Regards
Michel Received on Tue Oct 21 2008 - 09:56:37 CDT