• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1345
  • Last Modified:

TIMEZONE AS PARAMETER IN ORACLE REPORTS

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
SNEHA04
Asked:
SNEHA04
  • 3
1 Solution
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
neo9414Commented:
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
 
neo9414Commented:
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
 
neo9414Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now