Extracts and returns the value of a specified datetime field from a datetime

-- Values That Can Be Extracted:

-- YEAR
-- MONTH
-- DAY
-- HOUR
-- MINUTE
-- SECOND
-- TIMEZONE_HOUR
-- TIMEZONE_MINUTE
-- TIMEZONE_REGION
-- TIMEZONE_ABBR

EXTRACT ( FROM )
SELECT EXTRACT(YEAR FROM DATE '2005-01-04') FROM dual;

CREATE TABLE extract_test (
person_id NUMBER(3),
hire_date TIMESTAMP WITH TIME ZONE)
TABLESPACE data_sml;

INSERT INTO extract_test VALUES (100, SYSTIMESTAMP+1);
INSERT INTO extract_test VALUES (200, SYSTIMESTAMP+34);

SELECT EXTRACT(TIMEZONE_HOUR FROM hire_date)
FROM extract_test

ANSI date-time datatypes and arithmetic

ALTER session SET nls_date_format = 'YYYY-MM-DD';

-- old
SELECT ADD_MONTHS(TO_DATE('2003-07-31','YYYY-MM-DD'),-1) FROM dual;

ADD_MONTHS
----------
2003-06-30

-- new
SELECT TO_TIMESTAMP('2003-07-31','YYYY-MM-DD') - INTERVAL '1' MONTH FROM dual;

ERROR AT line 1:
ORA-01839: DATE NOT valid FOR MONTH specified

-- old
SELECT TO_DATE('2003-07-31','YYYY-MM-DD') - 100 FROM dual;

-- new (broken)
SELECT TO_TIMESTAMP('2003-07-31','YYYY-MM-DD') - INTERVAL '100' DAY FROM dual;

ERROR AT line 1:
ORA-01873: the leading precision OF the INTERVAL IS too small

-- new (note the extra "(3)")
SELECT TO_TIMESTAMP('2003-07-31','YYYY-MM-DD') - INTERVAL '100' DAY(3) FROM dual;

TO_TIMESTAMP('2003-07-31','YYYY-MM-DD')-INTERVAL'100'DAY(3)
-------------------------------------------------------------
2003-04-22 00:00:00