Solved

Oracle SQL Sysdate Time Zone Problem

Posted on 2009-07-16
10
1,134 Views
Last Modified: 2013-12-19
I have a table order. It has two columns apmt_date and apmt_time_zone.
we are storing time zones like P, M, C, E and empty. TIme Zones must be appended ny T at last.
That means P = PT, C = CT etc.

Now i have query like below
select * from order where apmt_date > sysdate.

Iam having problem here as iam not checking with time zones. I dont know the time zone of that oracle db instance.

I want a query which will also take care of time zones.

Please help,
Rave
0
Comment
[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
10 Comments
 
LVL 6

Expert Comment

by:tangchunfeng
ID: 24868119
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm
0
 
LVL 7

Expert Comment

by:grzessio
ID: 24868157
you could youse oracle new_time statement, but i do not know how you could convert your time zones to oracle time zones:
select * from V$TIMEZONE_NAMES;


your query:
select * from order where new_time(apmt_date, 'from_time_zone', 'to_time_zone') > sysdate

Open in new window

0
 
LVL 48

Accepted Solution

by:
schwertner earned 500 total points
ID: 24868344
Check this and make it workable. Fix it with hours of the timezones.

SELECT CASE when apmt_time_zone = '' OR 'E' then apmt_date
                     when apmt_time_zone =  'C' then apmt_date + 1/24
                     when apmt_time_zone =   'M' then apmt_date + 2/24
                     when apmt_time_zone =   'P' then apmt_date + 3/24
              END
FROM order;

After that try this:

select * from order
where CASE when apmt_time_zone = '' OR 'E' then apmt_date
                     when apmt_time_zone =  'C' then apmt_date + 1/24
                     when apmt_time_zone =   'M' then apmt_date + 2/24
                     when apmt_time_zone =   'P' then apmt_date + 3/24
              END
> sysdate
               
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 24868913
to extract your database's current timezone offset  you can use EXTRACT

select extract(timezone_hour from systimestamp) from dual
0
 

Author Comment

by:Ravi Kiran Reddy Katkuri
ID: 24878509
select  hdr.apmt_dt, hdr.apmt_time_zone,
        decode(APMT_TIME_ZONE,'CT',apmt_dt,
                              'ET',apmt_dt - 1/24,
                              'MT',apmt_dt + 1/24,
                              'PT',apmt_dt + 2/24,
                              apmt_dt)
from        uop_ord_hdr hdr

The above query will give the desired result.
Any how a lot for all of your inputs.
Thanks a lot,
Rave
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24878551
The author's proposed solution is only a minor variation from a previously posted solution
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24878571
Points are not everything in life.
The main thing is that the asker is satisfied by his answer.
I have no objections.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

729 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