Solved

Oracle SQL Sysdate Time Zone Problem

Posted on 2009-07-16
10
1,139 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
Independent Software Vendors: 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

617 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