{"id":19630,"date":"2021-08-24T08:08:06","date_gmt":"2021-08-23T22:08:06","guid":{"rendered":"https:\/\/database.guide\/?p=19630"},"modified":"2021-08-24T08:08:06","modified_gmt":"2021-08-23T22:08:06","slug":"sys_extract_utc-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/sys_extract_utc-function-in-oracle\/","title":{"rendered":"SYS_EXTRACT_UTC() Function in Oracle"},"content":{"rendered":"\n

In Oracle Database, the SYS_EXTRACT_UTC()<\/code> function extracts the UTC (Coordinated Universal Time) from a datetime value with time zone offset or time zone region name. <\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

SYS_EXTRACT_UTC(datetime_with_timezone)<\/code><\/pre>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate:<\/p>\n\n\n\n
SELECT \nSYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 +04:00')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
25\/AUG\/35 06:30:45.123456789 AM<\/pre>\n\n\n\n
The resulting format depends on the value of your NLS_TIMESTAMP_FORMAT<\/code> parameter. This parameter’s default value is derived from the NLS_TERRITORY<\/code> parameter. Changing any of these can change the format of the SYS_EXTRACT_UTC()<\/code> function for your session.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-RRRR HH24:MI:SSXFF';\nSELECT \nSYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 +04:00')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
25-AUG-2035 06:30:45.123456789<\/pre>\n\n\n\n
And here’s what it looks like when I set the NLS_TERRITORY<\/code> parameter to AMERICA<\/code>:<\/p>\n\n\n\n
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';\nSELECT \nSYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 +04:00')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
25-AUG-35 06.30.45.123456789 AM<\/pre>\n\n\n\n
Changing this parameter implicitly set the NLS_TIMESTAMP_FORMAT<\/code> to the format for AMERICA<\/code>.<\/p>\n\n\n\n
See How to Change your Session’s Date Format<\/a> for more information and examples.<\/p>\n\n\n\n
Time Zone Region Name<\/h2>\n\n\n\n
You can alternatively use the time zone region name instead of the time zone offset.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT \nSYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789 Asia\/Bangkok')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
25-AUG-35 03.30.45.123456789 AM<\/pre>\n\n\n\n
Default Time Zone<\/h2>\n\n\n\n
If a time zone is not specified, then the datetime is associated with the session time zone.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SELECT SYS_EXTRACT_UTC(TIMESTAMP '2035-08-25 10:30:45.123456789')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
25-AUG-35 12.30.45.123456789 AM<\/pre>\n\n\n\n
You can check the current session’s time zone with the SESSIONTIMEZONE<\/code> function:<\/p>\n\n\n\n
SELECT SESSIONTIMEZONE\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Australia\/Brisbane<\/pre>\n\n\n\n
See 4 Ways to Change the Time Zone in Oracle<\/a> for examples of changing this.<\/p>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
Passing null<\/code> results in an error:<\/p>\n\n\n\n
SELECT SYS_EXTRACT_UTC(null)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT SYS_EXTRACT_UTC(null)\nFROM DUAL\nError at Command Line : 1 Column : 24\nError report -\nSQL Error: ORA-30175: invalid type given for an argument\n30175. 00000 -  \"invalid type given for an argument\"\n*Cause:    There is an argument with an invalid type in the argument list.\n*Action:   Use the correct type wrapper for the argument.<\/pre>\n\n\n\n
Missing Argument<\/h2>\n\n\n\n
Calling the function with the wrong number of arguments, or without passing any arguments, results in an error:<\/p>\n\n\n\n
SELECT SYS_EXTRACT_UTC()\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT SYS_EXTRACT_UTC()\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00909: invalid number of arguments\n00909. 00000 -  \"invalid number of arguments\"\n*Cause:    \n*Action:<\/pre>\n","protected":false},"excerpt":{"rendered":"
In Oracle Database, the SYS_EXTRACT_UTC() function extracts the UTC (Coordinated Universal Time) from a datetime value with time zone offset or time zone region name.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[68,93,20],"class_list":["post-19630","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-date-functions","tag-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=19630"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19630\/revisions"}],"predecessor-version":[{"id":19700,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19630\/revisions\/19700"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=19630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=19630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=19630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}