Re: How do I get Timezone string ("07/18/64 00:00 EDT" from a DATE column?

Actually, you can't get timezone info from Oracle's TO_CHAR() function.

You got me thinking, cause I've used TO_CHAR() extensively, and I didn't recall ever seeing a timezone field. Well, that's cause there isn't one. It appears that Oracle stores date/time info based on the system clock of the server. Is this true? Oracle DOES provide a NEW_TIME() function, which will convert a date/time from one time zone to another. Note that it onlt works for timezones between Greenwich, England and Hawaii.

First, the short answer:

if you just wanted a date/time string with a static string for the timezone, yuo could append it to the date field like this:

SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI')||' EDT' FROM DUAL; Having said that.....
If you wanted a distributed application that really does timezones right, perhaps you could do something like this:

1.) On the client-side, use whatever local OS-dependent method to determine time zone.

2.) Write stored procedure on server-side that knows the timezone the server is running in. (That info could be hardcoded, or perhaps uo could get it from the host environment.)

3.) Call stored procedure (or function) from the client with the client side timezone, procedure will utilize the NEW_TIME() function to calculate date/time string in local client timezone.

4.) Use TO_CHAR() function to format as necessay, and concatenate the client-side timezone string.

Unproven, but I think it makes sense.

-Mark J. Bobak
Oracle Developer, UMI
In article <6cgasv$b8$1_at_usenet88.supernews.com>,   "Rick Slingwine" <_at_mitec.net> wrote:
>
> Lookup the to_char Oracle function. You can get just about any date format
> you can imagine.
>
> Rick.
>
> Bob Kuhar wrote in message <01bd3ce3$1f406ac0$5e564893_at_rek-4dx2-50-95>...
> >I am but a lowly PowerBuilder developer hoping that the Oracle masters can
> >enlighten me. The problem is this, PB Clients can't display Timezone info
> >in their DateTime fields. No way. No how. PB has failed, but I am
> >hopeful that Oracle Stored Procedures or Functions or something can provide
> >me with the Date/Time string of my dreams. All I want from my SELECTs of
> >DATE columns is a string of the format DD/MM/YYYY HH:MI TZ (Ex. "07/18/1964
> >00:00:00 EDT"). Has anyone else ever required Timezone from their Oracle
> >7.2 DATE fields?
> >
> >Thanks in advance.
> >Bob
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Feb 20 1998 - 00:00:00 CET