dbTalk Databases Forums  

how to get the date range

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc how to get the date range

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss how to get the date range in the comp.databases.oracle.misc forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
Michel Cadot
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 09:56 AM






<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




Reply With Quote
  #12  
Old   
Michel Cadot
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 09:56 AM






<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




Reply With Quote
  #13  
Old   
Michel Cadot
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 09:56 AM



<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




Reply With Quote
  #14  
Old   
Ed Prochak
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 01:00 PM


On Oct 21, 2:29*am, 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'),
[...]

Quote:
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?
I have an idea on how to control model railroad trains. Want to hear
it?
(Well you did say "Any ideas?" 8^)

How are you running the script? toad? sql*Plus? PERL DBI? Pro*C? The
answer depends on how you are running the query.

Assuming SQL*Plus, look up ACCEPT in the fine user manual. And BTW,
also pay attention to the advice Jim and Sybrand gave you.

HTH,
Ed


Reply With Quote
  #15  
Old   
Ed Prochak
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 01:00 PM


On Oct 21, 2:29*am, 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'),
[...]

Quote:
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?
I have an idea on how to control model railroad trains. Want to hear
it?
(Well you did say "Any ideas?" 8^)

How are you running the script? toad? sql*Plus? PERL DBI? Pro*C? The
answer depends on how you are running the query.

Assuming SQL*Plus, look up ACCEPT in the fine user manual. And BTW,
also pay attention to the advice Jim and Sybrand gave you.

HTH,
Ed


Reply With Quote
  #16  
Old   
Ed Prochak
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 01:00 PM


On Oct 21, 2:29*am, 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'),
[...]

Quote:
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?
I have an idea on how to control model railroad trains. Want to hear
it?
(Well you did say "Any ideas?" 8^)

How are you running the script? toad? sql*Plus? PERL DBI? Pro*C? The
answer depends on how you are running the query.

Assuming SQL*Plus, look up ACCEPT in the fine user manual. And BTW,
also pay attention to the advice Jim and Sybrand gave you.

HTH,
Ed


Reply With Quote
  #17  
Old   
Ed Prochak
 
Posts: n/a

Default Re: how to get the date range - 10-21-2008 , 01:00 PM


On Oct 21, 2:29*am, 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'),
[...]

Quote:
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?
I have an idea on how to control model railroad trains. Want to hear
it?
(Well you did say "Any ideas?" 8^)

How are you running the script? toad? sql*Plus? PERL DBI? Pro*C? The
answer depends on how you are running the query.

Assuming SQL*Plus, look up ACCEPT in the fine user manual. And BTW,
also pay attention to the advice Jim and Sybrand gave you.

HTH,
Ed


Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top