EXTRACT

Extracts the a portion of a date, time, or timestamp value.

Syntax

EXTRACT(<ValueToExract> FROM <DateTimeExpression>)
EXTRACT(<ValueToExract>, <DateTimeExpression>)

<ValueToExtract> =

YEAR
MONTH
WEEK
DAYOFWEEK
DAYOFYEAR
DAY
HOUR
MINUTE
SECOND
MSECOND

<DateTimeExpression> =

DATE
TIME
TIMESTAMP

Returns

INTEGER

Usage

The EXTRACT function extracts a designated portion of a date, time, or timestamp value and returns it. The following table details which portions can be extracted from which types:

Portion

Types

YEAR	DATE TIMESTAMP
MONTH	DATE TIMESTAMP
WEEK	DATE TIMESTAMP
DAYOFWEEK	DATE TIMESTAMP
DAYOFYEAR	DATE TIMESTAMP
DAY	DATE TIMESTAMP
HOUR	TIME TIMESTAMP
MINUTE	TIME TIMESTAMP
SECOND	TIME TIMESTAMP
MSECOND	TIME TIMESTAMP

All day and week values returned from EXTRACT follow the ISO 8601 standard for day and week numbers.

Examples

SELECT *
FROM Transactions
WHERE EXTRACT(YEAR FROM TransDateTime) = 2006

SQL 2003 Standard Deviations

This function deviates from the SQL 2003 standard in the following ways:

Deviation

Details

None

More Support Options

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Incident Reports

Roadmap