Oracle SQL Sysdate Time Zone Problem

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
Ravi Kiran Reddy KatkuriAnalyst ProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tangchunfengCommented:
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm
0
grzessioCommented:
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
schwertnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
to extract your database's current timezone offset  you can use EXTRACT

select extract(timezone_hour from systimestamp) from dual
0
Ravi Kiran Reddy KatkuriAnalyst ProgrammerAuthor Commented:
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
sdstuberCommented:
The author's proposed solution is only a minor variation from a previously posted solution
0
schwertnerCommented:
Points are not everything in life.
The main thing is that the asker is satisfied by his answer.
I have no objections.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.