EXTRACT() - Extract components of a time value

Function group: numeric function

EXTRACT() selects the specified component from a time value.
EXTRACT() uses the Gregorian calendar to do this, including the dates before its introduction on 10/15/1582.

---

EXTRACT ( component FROM expression )

component  ::= { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |

                YEAR_OF_WEEK | WEEK_OF_YEAR | DAY_OF_WEEK | DAY_OF_YEAR }

---

component

Specification of the component. Permissible entries:

YEAR	selects the year of timestamp or date, e.g. 2013
MONTH	selects the month of the year of a timestamp or date, e.g. 2 for February
DAY	selects the day of the month of a timestamp or date, e.g. 25
HOUR	selects the hour of the day of a timestamp or of a time, e.g. 23
MINUTE	selects the minute of the hour of a timestamp or of a time, e.g. 58
SECOND	selects the second of the minute of a timestamp or of a time, e.g. 35.765
YEAR_OF_WEEK	determines the year in which the week of a timestamp or day lies, e.g. 2013
WEEK_OF_YEAR	determines the week of the year of a timestamp or date, e.g. 52
DAY_OF_WEEK	determines the day of the week of a timestamp or date, e.g. 3 for Wednesday
DAY_OF_YEAR	determines the day of the year of a timestamp or date, e.g. 365

expression

Time value expression. Permissible types are:

• • • TIMESTAMP is permissible for every component

    • TIME with component HOUR, MINUTE or SECOND

    • DATE with component YEAR, MONTH, DAY, YEAR_OF_WEEK, WEEK_OF_YEAR, DAY_OF_WEEK or DAY_OF_YEAR

expression may not be a multiple value with dimension > 1.

Result

When expression returns the NULL value, the result is the NULL value.

Otherwise:

The corresponding numeric value.

Datentyp:

DECIMAL(1,0) DECIMAL(2,0) DECIMAL(3,0) DECIMAL(4,0) DECIMAL(5,3)

with component DAY_OF_WEEK with component MONTH, DAY, HOUR, MINUTE, WEEK_OF_YEAR with component DAY_OF_YEAR with component YEAR und YEAR_OF_WEEK with component SECOND

Examples

Determining the current year number.

EXTRACT (YEAR FROM CURRENT_DATE)

Determining the day in the year.

EXTRACT (DAY_OF_YEAR FROM DATE '<date>')

Determining the current second.

EXTRACT (SECOND FROM CURRENT_TIME(3))