EXTRACT

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, built-in character string, or built-in graphic string data type.

If date-expression is a character or graphic string, 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, built-in character string, or built-in graphic string data type.

If timestamp-expression is a character or graphic string, 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. Returns 0 for a date expression. 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. Returns 0 for a date expression. The result is identical to the MINUTE scalar function. For more information, see MINUTE.

SECOND

Specifies that the second portion of the time or timestamp expression is returned. Returns 0 for a date expression. The result is identical to:

• SECOND(expression, 6) when the data type of expression is a TIME value or a string representation of a TIME or TIMESTAMP.
• SECOND(expression, s) when the data type of expression is a TIMESTAMP(s) value.

For more information, see SECOND.

date-expression

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

If date-expression is a character or graphic string, its value must be a valid character-string or graphic-string representation of a date. If expression is a valid string representation of a date, it must be one of the IBM® SQL standard formats. For the valid formats of string representations of dates, see String representations of datetime values.

time-expression

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

If time-expression is a character or graphic string, its value must be a valid character-string or graphic-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, built-in character string, or built-in graphic string data type.

If timestamp-expression is a character or graphic string, 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.

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

• If YEAR, MONTH, DAY, HOUR, or MINUTE is specified, the data type of the result is INTEGER.
• If SECOND is specified with a TIMESTAMP(p) value, the data type of the result is DECIMAL(2+p,p) where p is the fractional seconds precision.
• If SECOND is specified with a TIME value or a string representation of a TIME or TIMESTAMP, the data type of the result is DECIMAL(8,6).

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