Oracle PL/SQL/Date Timezone/EXTRACT

EXTRACT(DAY FROM TO_DATE("01-JAN-15:26", "DD-MON-YYYY HH24:MI:SS"))

SQL>
SQL> select EXTRACT(DAY FROM TO_DATE("01-JAN-2005 19:15:26",
  2      "DD-MON-YYYY HH24:MI:SS")) AS DAY from dual;
       DAY
----------
         1
SQL>

EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP()

SQL>
SQL>
SQL> --EXTRACT(): get the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP():
SQL>
SQL> SELECT
  2    EXTRACT(HOUR FROM TO_TIMESTAMP("01-JAN-2005 19:15:26",
  3      "DD-MON-YYYY HH24:MI:SS")) AS HOUR,
  4    EXTRACT(MINUTE FROM TO_TIMESTAMP("01-JAN-2005 19:15:26",
  5      "DD-MON-YYYY HH24:MI:SS")) AS MINUTE,
  6    EXTRACT(SECOND FROM TO_TIMESTAMP("01-JAN-2005 19:15:26",
  7      "DD-MON-YYYY HH24:MI:SS")) AS SECOND
  8  FROM dual;
      HOUR     MINUTE     SECOND
---------- ---------- ----------
        19         15         26
SQL>

EXTRACT(MONTH FROM TO_DATE("01-JAN-15:26", "DD-MON-YYYY HH24:MI:SS"))

SQL>
SQL> select  EXTRACT(MONTH FROM TO_DATE("01-JAN-2005 19:15:26",
  2      "DD-MON-YYYY HH24:MI:SS")) As MONTH from dual;
     MONTH
----------
         1
SQL>

EXTRACT(): return a year, month, day, hour, minute, second, or time zone from x

SQL>
SQL>
SQL> -- EXTRACT(): return a year, month, day, hour, minute, second, or time zone from x;
SQL>
SQL> SELECT EXTRACT(YEAR FROM TO_DATE("01-JAN-2005 19:15:26","DD-MON-YYYY HH24:MI:SS")) AS YEAR FROM dual;
      YEAR
----------
      2005
SQL>

EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ("01-JAN-15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))

SQL>
SQL> select EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(
  2      "01-JAN-2005 19:15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))
  3      AS TZA from dual;
TZA
----------
PST
SQL>

EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ("01-JAN-15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM")

SQL>
SQL> --EXTRACT(): get the time zone hour, minute, second, region, and region abbreviation from a TIMESTAMP WITH TIMEZONE returned by TO_TIMESTAMP_TZ():
SQL>
SQL> SELECT
  2    EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(
  3      "01-JAN-2005 19:15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM"))
  4      AS TZH
  5  FROM dual;
       TZH
----------
        -7
SQL>
SQL>

EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ("01-JAN-15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM"))

SQL>
SQL>
SQL> select EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(
  2      "01-JAN-2005 19:15:26 -7:15", "DD-MON-YYYY HH24:MI:SS TZH:TZM"))
  3      AS TZM from dual;
       TZM
----------
       -15

EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ("01-JAN-15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))

SQL>
SQL> select EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(
  2      "01-JAN-2005 19:15:26 PST", "DD-MON-YYYY HH24:MI:SS TZR"))
  3      AS TZR from dual;
TZR
----------------------------------------------------------------
PST

select extract( year from add_months(sysdate,36) ) "3 Years Out"

 
SQL>
SQL> select extract( year from add_months(sysdate,36) ) "3 Years Out"
  2  from dual;
3 Years Out
-----------
       2011
1 row selected.
SQL>
SQL>
SQL> --

The EXTRACT() function returns the value of a specified datetime field from a datetime or interval value expression.

 
SQL>
Syntax: EXTRACT([[YEAR]|[MONTH]|[DAY]|[HOUR]|[MINUTE]|[SECOND]]|
[[TIMEZONE_HOUR]|[TIMEZONE_MINUTE]|[TIMEZONE_REGION]|[TIMEZONE_ABBR])
SQL>
SQL>
SQL> SELECT
  2     EXTRACT(YEAR FROM SYSDATE) current_year
  3  FROM dual;
CURRENT_YEAR
------------
        2008
SQL>
SQL>