Extracting Date and Time Information

Go Up to DATE, TIME, and TIMESTAMP Data Types

The EXTRACT() function extracts date and time information from databases. In dialect 3, the EXTRACT operator allows you to return different parts of a TIMESTAMP value. The EXTRACT operator makes no distinction between dialects when formatting or returning the information. EXTRACT() has the following syntax:

EXTRACT (<part> FROM <value>)

The value passed to the EXTRACT() expression must be DATE, TIME, or TIMESTAMP. Extracting a part that doesn’t exist in a data type results in an error. For example:

EXTRACT (TIME FROM aTime)

A statement such as EXTRACT (YEAR from aTime) would fail.

The data type of EXTRACT() expressions depends on the specific part being extracted:

Extract	Resulting data type	Representing
YEAR	SMALLINT	Year, range 0-5400
MONTH	SMALLINT	Month, range 1-12
DAY	SMALLINT	Day, range 1-31
HOUR	SMALLINT	Hour, range 1-23
MINUTE	SMALLINT	Minute, range 1-59
SECOND	DECIMAL(6,4)	Second, range 0-59.9999
WEEKDAY	SMALLINT	Day of the week, range 0-6 (0 = Sunday, 1 = Monday, and so on)
YEARDAY	SMALLINT	Day of the year, range 1-366

SELECT EXTRACT (YEAR FROM timestamp_fld) FROM table_name;
=======
1999
SELECT EXTRACT (YEAR FROM timestamp_fld) FROM table_name;
=======
1999
SELECT EXTRACT (MONTH FROM timestamp_fld) FROM table_name;
=======
6
SELECT EXTRACT (DAY FROM timestamp_fld) FROM table_name;
=======
25
SELECT EXTRACT (MINUTE FROM timestamp_fld) FROM table_name;
=======
24
SELECT EXTRACT (SECOND FROM timestamp_fld) FROM table_name;
============
35.0000
SELECT EXTRACT (WEEKDAY FROM timestamp_fld) FROM table_name;
=======
5
SELECT EXTRACT (YEARDAY FROM timestamp_fld) FROM table_name;
=======
175
SELECT EXTRACT (MONTH FROM timestamp_fld) ||
'-' || EXTRACT (DAY FROM timestamp_fld) ||
'-' || EXTRACT (YEAR FROM timestamp_fld) FROM table_name;
====================
6-25-1999

Advance To:

• DECIMAL and NUMERIC Data Types