Re: how to get the date range

<muhammaddzulkarnain_at_gmail.com> wrote in message 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?
>
>

Use dates not strings! You are crippling the optimizer . Ugh. Jim Received on Tue Oct 21 2008 - 06:23:21 CDT