Home » SQL & PL/SQL » SQL & PL/SQL » Timezone conversion (Oracle10g, Windows XP)
Timezone conversion [message #310948] Wed, 02 April 2008 23:23 Go to next message
reachneelima
Messages: 3
Registered: April 2008
Junior Member
Hi,

There is a column(NVARCHAR2 (50)) in my table which stores the timezone information.
The data in it can be for eg 'GMT' or with some offset like 'GMT +05:30'.
I need to write query which will extract the equivalent timezone string for me.

Ideally it should return 'GMT' if the data in the column is 'GMT' or
it should return 'PST' if the value in the column is 'GMT-8'.

How to do this? Please help.

Thanks,
Neelima
Re: Timezone conversion [message #310949 is a reply to message #310948] Wed, 02 April 2008 23:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>There is a column(NVARCHAR2 (50)) in my table which stores the timezone information.
When a person finds himself in a hole, the FIRST thing they should do is STOP digging!

NEVER, EVER never, ever store date/time/timezone data in a (N)VARCHAR2 field.

I refuse to assist you making a BAD situation worse by compounding the original design error.
Re: Timezone conversion [message #310951 is a reply to message #310949] Wed, 02 April 2008 23:33 Go to previous messageGo to next message
reachneelima
Messages: 3
Registered: April 2008
Junior Member
I too can't do anything about it since I am not the owner of
those tables. They are already there and I can't change the
design of those tables. All I want now is to extract the data
somehow.

Thanks,
Neelima
Re: Timezone conversion [message #310953 is a reply to message #310948] Wed, 02 April 2008 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Start writing PL/SQL.
Now, you get to reinvent the wheel.
Start writing PL/SQL to handle the many & numerous combinations & permutations of strings which can represent DATE datatypes.

There is NOTHING in your application prevent the storing of 'ID10T' your column(NVARCHAR2 (50)) & how do plan on deciding what timezone it contains.
Re: Timezone conversion [message #310956 is a reply to message #310948] Wed, 02 April 2008 23:47 Go to previous messageGo to next message
reachneelima
Messages: 3
Registered: April 2008
Junior Member
That was very rude!

************************
Can someone please help me out?
Assumption: Values will be stored correctly in the column
and there won't be any junk data.

Thanks,
Neelima

Re: Timezone conversion [message #310960 is a reply to message #310948] Wed, 02 April 2008 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Trust me.
>and there won't be any junk data.
You can NOT,NOT guarentee that!
Re: Timezone conversion [message #310971 is a reply to message #310948] Thu, 03 April 2008 00:14 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no built-in function for this, you have to create your own Java function using java.util.TimeZone classes.

Regards
Michel
Previous Topic: Need Help in creating table with BLOB Column
Next Topic: Cursor Question
Goto Forum:
  


Current Time: Thu Dec 08 20:06:39 CST 2016

Total time taken to generate the page: 0.10220 seconds