Oracle PL/SQL/Date Timezone/EXTRACT

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

   <source lang="sql">

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>

      </source>
   
  

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

   <source lang="sql">

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>

      </source>
   
  

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

   <source lang="sql">

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>

      </source>
   
  

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

   <source lang="sql">

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>

      </source>
   
  

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

   <source lang="sql">

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>

      </source>
   
  

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

   <source lang="sql">

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>

      </source>
   
  

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

   <source lang="sql">

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

      </source>
   
  

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

   <source lang="sql">

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

      </source>
   
  

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

   <source lang="sql">

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

</source>
   
  

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

   <source lang="sql">

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>

</source>