function

Oracle FROM_TZ Function is used to convert a TIMESTAMP value to another TIMASTAMP with TIME ZONE value. Oracle FROM_TZ Function can be used with the SQL TRUNC Function and SQL TO_CHAR Function.

Oracle FROM_TZ Function Syntax

SELECT FROM_TZ(timestamp_value,timezone_value)
FROM table_name;

‘timestamp_value’ is the value that has to be converted to a TIMESTAMP with TIME ZONE value
‘timezone_value’ is the time zone that is used to convert the time stamp value to time stamp with time zone value .

Oracle FROM_TZ Function – Using SQL SELECT Statement Example

Oracle FROM_TZ Function is used with the SQL SELECT Statement.

For example, the Oracle FROM_TZ Function query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value.

SELECT FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '5:30')
FROM DUAL;

Will return “12/15/2012 1:50:42.000000000 AM +05:30”

Oracle FROM_TZ Function – Using SQL TRUNC Function Example

Oracle FROM_TZ Function can be used with the SQL TRUNC Function.

For example, the SQL query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value and returns only the date component.

SELECT TRUNC(FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '-7:00'))
FROM DUAL;

Will return “12/15/2012″.

Oracle FROM_TZ Function – Using SQL TO_CHAR Function Example

Oracle FROM_TZ Function can be used with the SQL TO_CHAR Function.

For example, the Oracle FROM_TZ Function query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value and spells the Month.

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '-7:00'),'DD-MONTH-RRRR')
FROM DUAL;

Will return “15-DECEMBER -2012″.

Oracle TZ_OFFSET Function

November 5, 2012 by techhoneyadmin

Oracle TZ_OFFSET Function is used to return the TIMEZONE offset of a value.

Oracle TZ_OFFSET Function Syntax

SELECT TZ_OFFSET(time_zone)
FROM table_name;

‘time_zone’ can be any time zone, a time zone offset from UTC, a sessiontimezone or a dbtimezone.

Examples of Timezones:-

Canada/Atlantic	Pacific/Easter
Canada/Central	Pacific/Honolulu
Canada/East-Saskatchewan	Pacific/Kwajalein
Canada/Eastern	Pacific/Pago_Pago
Canada/Mountain	Pacific/Samoa
Canada/Newfoundland	US/Alaska
Canada/Pacific	US/Aleutian
Canada/Saskatchewan	US/Arizona
Canada/Yukon	US/Central
Europe/Dublin	US/East-Indiana
Europe/Istanbul	US/Eastern
Europe/Lisbon	US/Hawaii
Europe/London	US/Michigan
Europe/Moscow	US/Mountain
Europe/Warsaw	US/Pacific
Greenwich	US/Pacific-New
Pacific/Auckland	US/Samoa
Pacific/Chatham

Using the below SQL SELECT Statement you can view all the Timezones available.

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

Oracle TZ_OFFSET – Using SQL SELECT Example

Oracle TZ_OFFSET Function is used with the SQL SELECT Statement.

For example, the Oracle TZ_OFFSET query below returns the Time Zone offset of “Asia/Tokyo” zone.

SELECT TZ_OFFSET('Asia/Tokyo')
FROM DUAL;

Will return “+09:00” as the offset for the ASIA/TOKYO time zone.

Oracle TZ_OFFSET – Using SESSIONTIMEZONE Example

Oracle TZ_OFFSET can be used with the SQL SESSIONTIMEZONE Function.

For example, the Oracle TZ_OFFSET query below returns the Timezone offset of the current session of the database.

SELECT TZ_OFFSET(SESSIONTIMEZONE)
FROM DUAL;

May return something like “+05:30” depending upon the session’s timezone offset.

Oracle TZ_OFFSET – Using DBTIMEZONE Example

Oracle TZ_OFFSET can be used with the SQL DBTIMEZONE to fetch the database time zone offset.

For example, the SQL TZ_OFFSET query below returns the timezone offset for the database.

SELECT TZ_OFFSET(DBTIMEZONE)
FROM DUAL;

May return something like “+00:00” depending upon the database timezone settings.

Oracle LOCALTIMESTAMP Function

November 5, 2012 by techhoneyadmin

Oracle LOCALTIMESTAMP Function returns current date and time of the current SQL session.
Oracle LOCALTIMESTAMP returns the TIMESTAMP value.

Oracle LOCALTIMESTAMP Function Syntax

SELECT LOCALTIMESTAMP
FROM table_name;

Oracle LOCALTIMESTAMP returns date and time as set by the ALTER SESSION statement.

Oracle LOCALTIMESTAMP – Using SQL SELECT Statement Example

Oracle LOCALTIMESTAMP is used with SQL SELECT Statement to fetch current date and timestamp.

For example, the below SQL SELECT query will return current date and timestamp.

SELECT LOCALTIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:09:00.807000 AM”

Oracle LOCALTIMESTAMP – Using ALTER SESSION Example

We can use the ALTER SESSION SET TIME_ZONE to change the time zone of the database.

For example, the SQL SELECT query below will return the date and time after we have altered the database timezone.

ALTER SESSION SET TIME_ZONE = '-7:0';

And then we query the LOCALTIMESTAMP as

SELECT LOCALTIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:09:21.186000 AM” because the time zone has been altered

Oracle LOCALTIMESTAMP – Using TRUNC Function Example

LOCALTIMESTAMP Function can be used with the SQL TRUNC Function to remove the timestamp part.

For example, the SQL query below returns only the date part from Oracle LOCALTIMESTAMP Function.

SELECT TRUNCT(localtimestamp)
FROM dual;

May return something like “11/5/2012”.

Oracle NEW_TIME Function

November 5, 2012 by techhoneyadmin

Oracle NEW_TIME Function is used to return the date in one time zone to a date in another time zone.

Oracle NEW_TIME Function Syntax

SELECT NEW_TIME(date,time_zone1, time_zone2)
FROM table_name;

Here in the above Oracle NEW_TIME Function Syntax:

‘date’ is the date that has to be seen in another time zone.
‘time_zone1’ is the time zone from where the passed date is to be converted
‘time_zone2’ is the time zone to which the passed date is to be converted

Time_zone1 and time_zone2 can take the following values:

Value	Explanation
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

Oracle NEW_TIME – Converting GMT to PST Example

Oracle NEW_TIME Function can convert the time from one time zone to another.

For example, the Oracle NEW_TIME Function query below converts the GMT time to PST.

SELECT NEW_TIME(SYSDATE,'GMT','PST')
FROM DUAL;

Will return something like “11/5/2012 4:24:04 AM”, here we have converted the sysdate time from GMT to PST time zone.

Oracle NEW_TIME Function – Use with SQL TO_DATE Example

Oracle NEW_TIME Function can be used with the SQL TO_DATE Function.

For example, the Oracle NEW_TIME Function query below uses SQL TO_DATE Function to convert String to Date in GMT and then returns PST.

SELECT NEW_TIME(TO_DATE('01-JAN-2012 02:30','DD-MON-YYYY HH24:MI'),PST,GMT)
FROM DUAL;

Will return something like “1/1/2012 10:30:00 AM” because we have converted the Pacific Standard Time to Greenwich Mean Time.