Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle SQL Sysdate Time Zone Problem

Posted on 2009-07-16
10
Medium Priority
?
1,145 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 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

722 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