?
Solved

TIMEZONE AS PARAMETER IN ORACLE REPORTS

Posted on 2005-03-02
7
Medium Priority
?
1,330 Views
Last Modified: 2008-02-01
HI,

We deal with timezone in our databases.  if it is EPT between apr to oct , then it is EST date +1/24 or else it is same as est.
EPT EASTERN PREVAILING TIME
EST EASTERN STANDARD TIME

How can i incorporate this in reports , that when the user pulls the est / ept from drop down list, it automatically gives the data for the corresponding timezone.

any help
0
Comment
Question by:SNEHA04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13439628
Set up a view that returns the time you need (you can use a PL\SQL function in the select statement of the view if you cannot get the correct value with just a "decode" or "case" in the query) then in your report, base the dropdown on the view.
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13439689
here it is

select to_char(new_time( '&datetime','EST',
           case when to_char('&datetime','mm') between 4 and 10 then
           decode('&timezone','EST','EST','ESP','EDT')
           ELSE 'EST'
           END),
'dd-mon-yyyy hh24:mi:ss') from dual;

eg:
select to_char(new_time( sysdate,'EST',
           case when to_char(sysdate,'mm') between 4 and 10 then
           decode('&timezone','EST','EST','ESP','EDT')
           ELSE 'EST'
           END),
'dd-mon-yyyy hh24:mi:ss') from dual;

This will work only on Oracle 9i and above as it has case statement.
0
 
LVL 9

Expert Comment

by:neo9414
ID: 13439723
error in the above post. instead of ESP it should be EPT

here it is

select to_char(new_time( '&datetime','EST',
           case when to_char('&datetime','mm') between 4 and 10 then
           decode('&timezone','EST','EST','EPT','EDT')
           ELSE 'EST'
           END),
'dd-mon-yyyy hh24:mi:ss') from dual;

eg:
select to_char(new_time( sysdate,'EST',
           case when to_char(sysdate,'mm') between 4 and 10 then
           decode('&timezone','EST','EST','EPT','EDT')
           ELSE 'EST'
           END),
'dd-mon-yyyy hh24:mi:ss') from dual;

This will work only on Oracle 9i and above as it has case statement.
0
 
LVL 9

Accepted Solution

by:
neo9414 earned 2000 total points
ID: 13439779
Here it is...This will work in other versions too....

eg:
select to_char(new_time( sysdate,'EST',
           decode(to_char(sysdate,'mm'),4,decode('&timezone','EST','EST','EPT','EDT')
                                       ,5,decode('&timezone','EST','EST','EPT','EDT')
                                       ,6,decode('&timezone','EST','EST','EPT','EDT')
                                       ,7,decode('&timezone','EST','EST','EPT','EDT')
                                       ,8,decode('&timezone','EST','EST','EPT','EDT')
                                       ,9,decode('&timezone','EST','EST','EPT','EDT')
                                       ,10,decode('&timezone','EST','EST','EPT','EDT')
                                       ,'EST')),
'dd-mon-yyyy hh24:mi:ss') from dual;

just give your date in place of sysdate and your timezone in place of &timezone (EST,EPT)

Hope this helps

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question