date format conversion

[SkyHigh]

Hi

I have to create a report where I have to show a date in a table with timezone, but when I query the date field it shows date as :

11-MAY-01

While I want it as : ( Time zone should be UTC )

2001-05-11 23:09:09+00 (This is just an example)

And also how can I add report heading using sqlplus

Your help would be much appreciated
Brenda

 

[CRoberts]

Oracle has a very robust set of date/time functions. The database has various defaults like dd-Mon-yy for a date. You can change from the default via using a function (like TO_CHAR or TO_DATE) or alter the session using an ALTER SESSION Looks like here you want something like (from Oracle's SQL Reference) :

The following example illustrates that CURRENT_TIMESTAMP is sensitive to the session time zone:

ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------------
-05:00 04-APR-00 01.17.56.917550 PM -05:00

ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ----------------------------------------------------
-08:00 04-APR-00 10.18.21.366065 AM -08:00


Check out this url to research time/date functions:

http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/index.htm