Select date in GMT format

NEW_TIME (oracle8i and above... pls check for older version)

NEW_TIME(date, zone1, zone2)
Convert between GMT and US time zones (but not CET)

zone1 and zone2 can be any of the following values:

Value Description
AST Atlantic Standard Time
ADT Atlantic Daylight Time
BST Bering Standard Time
BDT Bering Daylight Time
CST Central Standard Time
CDT Central Daylight Time
EST Eastern Standard Time
EDT Eastern Daylight Time
GMT Greenwich Mean Time
HST Alaska-Hawaii Standard Time
HDT Alaska-Hawaii Daylight Time
MST Mountain Standard Time
MDT Mountain Daylight Time
NST Newfoundland Standard Time
PST Pacific Standard Time
PDT Pacific Daylight Time
YST Yukon Standard Time
YDT Yukon Daylight Time



**Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.


Alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS' ;

SELECT NEW_TIME(TO_DATE( '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'), 'AST', 'GMT') "New Date and Time"
FROM dual;

Here AST is a Current (Server's) time format and GMT is a desire time format.


http://www.oracle.com/technology/oramag/code/tips2004/tip1.html