EXTRACT

Click to skip syntax diagram

Extract Date Values

Read syntax diagramSkip visual syntax diagram>>-EXTRACT--(--+-YEAR--+--FROM--+-date-expression------+--)----><
               +-MONTH-+        '-timestamp-expression-'
               '-DAY---'
 

Extract Time Values

Read syntax diagramSkip visual syntax diagram>>-EXTRACT--(--+-HOUR---+--FROM--+-time-expression------+--)---><
               +-MINUTE-+        '-timestamp-expression-'
               '-SECOND-'
 

The EXTRACT function returns a specified portion of a datetime value.

Extract Date Values

YEAR
Specifies that the year portion of the date or timestamp expression is returned. The result is identical to the YEAR scalar function. For more information, see YEAR.
MONTH
Specifies that the month portion of the date or timestamp expression is returned. The result is identical to the MONTH scalar function. For more information, see MONTH.
DAY
Specifies that the day portion of the date or timestamp expression is returned. The result is identical to the DAY scalar function. For more information, see DAY.
date-expression
An expression that returns the value of either a built-in date or built-in character string data type.

If expression is a character or graphic string, it must not be a CLOB or DBCLOB and its value must be a valid character-string or graphic-string representation of a date. For the valid formats of string representations of dates, see String representations of datetime values.

timestamp-expression
An expression that returns the value of either a built-in timestamp or built-in character string data type.

If expression is a character or graphic string, it must not be a CLOB or DBCLOB and its value must be a valid character-string or graphic-string representation of a timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.

Extract Time Values

HOUR
Specifies that the hour portion of the time or timestamp expression is returned. The result is identical to the HOUR scalar function. For more information, see HOUR.
MINUTE
Specifies that the minute portion of the time or timestamp expression is returned. The result is identical to the MINUTE scalar function. For more information, see MINUTE.
SECOND
Specifies that the second portion of the date or timestamp expression is returned. The result is identical to the following:
DECIMAL((DAY(expression) + DECIMAL(MICROSECOND(expression),12,6)/1000000), 8,6)
For more information, see SECOND and MICROSECOND.
time-expression
An expression that returns the value of either a built-in time or built-in character string data type.

If expression is a character string, it must not be a CLOB and its value must be a valid character-string representation of a time. For the valid formats of string representations of times, see String representations of datetime values.

timestamp-expression
An expression that returns the value of either a built-in timestamp or built-in character string data type.

If expression is a character string, it must not be a CLOB and its value must be a valid character-string representation of a timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.

The data type of the result of the function depends on the part of the datetime value that is specified:

If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Examples