how to get the date range

#1

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?

#2

<muhammaddzulkarnain (AT) gmail (DOT) com> wrote

Quote:

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

#3

<muhammaddzulkarnain (AT) gmail (DOT) com> wrote

Quote:

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

#4

<muhammaddzulkarnain (AT) gmail (DOT) com> wrote

Quote:

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

#5

<muhammaddzulkarnain (AT) gmail (DOT) com> wrote

Quote:

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

#6

On 21 okt, 09:29, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

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!

#7

On 21 okt, 09:29, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

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!

#8

On 21 okt, 09:29, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

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!

#9

On 21 okt, 09:29, muhammaddzulkarn... (AT) gmail (DOT) com wrote:

Quote:

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!

#10

<muhammaddzulkarnain (AT) gmail (DOT) com> a écrit dans le message de news: 0c6950cc-7197-4e38-9227-15514261e5eb...oglegroups.com...

Quote:

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