SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2005 19:15:26','DD-MON-YYYY HH24:MI:SS')) AS YEAR : EXTRACT « Date Timestamp Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Date Timestamp Functions
  3. EXTRACT
SQL> SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2005 19:15:26','DD-MON-YYYY HH24:MI:SS')) AS YEAR
  2  FROM dual;

      YEAR
----------
      2005

SQL>








13.6.EXTRACT
13.6.1.EXTRACT() to extract and return a year, month, day, hour, minute, second, or time zone from the timestamp types or a DATE.
13.6.2.SELECT EXTRACT(MONTH FROM TO_DATE('01-JAN-2005 19:15:26','DD-MON-YYYY HH24:MI:SS')) As MONTH
13.6.3.SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2005 19:15:26','DD-MON-YYYY HH24:MI:SS')) AS YEAR
13.6.4.EXTRACT() gets the hour, minute, and second from a TIMESTAMP returned by TO_TIMESTAMP()
13.6.5.EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS MINUTE
13.6.6.EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2005 19:15:26', 'DD-MON-YYYY HH24:MI:SS')) AS SECOND
13.6.7.EXTRACT()gets the time zone hour, minute, second, region, and region abbreviation from a TIMESTAMP WITH TIMEZONE returned by TO_TIMESTAMP_TZ()
13.6.8.EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))
13.6.9.EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))
13.6.10.EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))
13.6.11.extract year from birthday
13.6.12.extract month from birthday
13.6.13.extract day from birthday
13.6.14.Extract year, month, day from a date
13.6.15.Order date value by only year field with extract() function