OraFAQ Forum: SQL & PL/SQL » Timezone conversion

Home » SQL & PL/SQL » SQL & PL/SQL » Timezone conversion (Oracle10g, Windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Timezone conversion [message #310948]

Wed, 02 April 2008 23:23

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

Report message to a moderator

Re: Timezone conversion [message #310949 is a reply to message #310948]

Wed, 02 April 2008 23:29

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Timezone conversion [message #310951 is a reply to message #310949]

Wed, 02 April 2008 23:33

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

Report message to a moderator

Re: Timezone conversion [message #310953 is a reply to message #310948]

Wed, 02 April 2008 23:40

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Timezone conversion [message #310956 is a reply to message #310948]

Wed, 02 April 2008 23:47

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

Report message to a moderator