EXTRACT

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

Extract Date Values

Extract Time Values

Extract Date Values

EPOCH: Specifies that the number of seconds since 1970-01-01 00:00:00.00 is returned for the date-expression or timestamp-expression. The value can be positive or negative. This cannot be specified for a date-duration or timestamp-duration.
MILLENNIUM or MILLENNIUMS: Specifies that the number of full 1000-year periods represented by the year is returned for the date-expression, timestamp-expression, date-duration, or timestamp-duration. For example, 2 is returned for a date between 2000-01-01 and 2999-12-31.
CENTURY or CENTURIES: Specifies that the number of full 100-year periods represented by the year is returned for the date-expression, timestamp-expression, date-duration, or timestamp-duration. For example, 20 is returned for a date between 2000-01-01 and 2099-12-31.
DECADE or DECADES: Specifies that the number of full 10-year periods represented by the year is returned for the date-expression, timestamp-expression, date-duration, or timestamp-duration. For example, 201 is returned for a date between 2010-01-01 and 2019-12-31.
YEAR or YEARS: Specifies that the year portion of the date-expression, timestamp-expression, date-duration, or timestamp-duration is returned. The result is identical to the YEAR scalar function. For more information, see YEAR.
QUARTER: Specifies that the quarter of the year (1 - 4) is returned for the date-expression or timestamp-expression. The result is identical to the QUARTER scalar function. For more information, see QUARTER. This cannot be specified for a date-duration or timestamp-duration.
MONTH or MONTHS: Specifies that the month portion of the date-expression, timestamp-expression, date-duration, or timestamp-duration is returned. The result is identical to the MONTH scalar function. For more information, see MONTH.
WEEK: Specifies that the number of the week of the year (1 - 53) is returned for the date-expression or timestamp-expression. The week starts with Monday. The result is identical to the WEEK_ISO scalar function. For more information, see WEEK_ISO. This cannot be specified for a date-duration or timestamp-duration.
DAY or DAYS: Specifies that the day portion of the date-expression, timestamp-expression, date-duration or timestamp-duration is returned. The result is identical to the DAY scalar function. For more information, see DAY.
DOW: Specifies that the day of the week, where 1 represents Sunday and 7 represents Saturday, is returned for the date-expression or timestamp-expression. The result is identical to the DAYOFWEEK scalar function. For more information, see DAYOFWEEK. This cannot be specified for a date-duration or timestamp-duration.
DOY: Specifies that the day of the year (1 - 366) is returned for the date-expression or timestamp-expression. The result is identical to the DAYOFYEAR scalar function. For more information, see DAYOFYEAR. This cannot be specified for a date-duration or timestamp-duration.
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.
date-duration: A date duration expressed as a DECIMAL(8,0) number. For the valid formats of datetime durations, see Datetime operands and durations.
timestamp-duration: A timestamp duration expressed as a DECIMAL(14+s,s) number, where s is the number of digits of fractional seconds ranging from 0 to 12. For the valid formats of datetime durations, see Datetime operands and durations.

Extract Time Values

HOUR or HOURS

Specifies that the hour portion of the time-expression, timestamp-expression, Start of change

time-duration, or timestamp-duration End of change

is returned. Start of change

Returns 0 for a date-expression Start of change

or date-duration End of change

The result is identical to the HOUR scalar function. For more information, see HOUR.

MINUTE or MINUTES

Specifies that the minute portion of the time-expression, timestamp-expression, Start of change

time-duration, or timestamp-duration End of change

is returned. Start of change

Returns 0 for a date-expression Start of change

or date-duration End of change

The result is identical to the MINUTE scalar function. For more information, see MINUTE.

SECOND or SECONDS

Specifies that the second portion of the time-expression, timestamp-expression, Start of change

time-duration, or timestamp-duration End of change

is returned. Start of change

Returns 0 for a date-expression Start of change

or date-duration End of change

The result is identical to:

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

For more information, see SECOND. End of change

MILLISECOND or MILLISECONDS

Specifies the second of the minute, including fractional parts to one thousandth of a second, multiplied by 1000 is returned (0 - 59999) for the timestamp-expression or timestamp-duration. Returns 0 for a date-expression, time-expression, date-duration, or time-duration. End of change

MICROSECOND or MICROSECONDS

Specifies the second of the minute, including fractional parts to one millionth of a second, multiplied by 1000000 is returned (0 - 59999999) for the timestamp-expression or timestamp-duration. Returns 0 for a date-expression, time-expression, date-duration, or time-duration. End of change

date-expression

An expression that returns the value of either a built-in date. built-in character string, or Start of change

built-in graphic string End of change

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, Start of change

or built-in graphic string End of change

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, Start of change

or built-in graphic string End of change

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.

time-duration

A time duration expressed as a DECIMAL(6,0) number. For the valid formats of datetime durations, see Datetime operands and durations. End of change

timestamp-duration

A timestamp duration expressed as a DECIMAL(14+s,s) number, where s is the number of digits of fractional seconds ranging from 0 to 12. For the valid formats of datetime durations, see Datetime operands and durations. End of change

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

If EPOCH is specified, the data type of result is BIGINT.
If MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, DOW, DOY, HOUR, MINUTE, MILLISECOND, or MICROSECOND 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.

Examples

Assume the column PRSTDATE has an internal value equivalent to 1988-12-25.
```
  SELECT EXTRACT( MONTH FROM PRSTDATE )
    FROM PROJECT
```
This statement returns the integer value 12.
Assume the timestamp global variable GV1 has the value '2007-02-14 12:15:06.123456'.
```
  VALUES EXTRACT(MILLISECONDS FROM GV1);
```
This statement returns the integer value 6123.
Assume the timestamp global variable GV1 has the value '2007-02-14 12:15:06.123456'.
```
  VALUES EXTRACT(MICROSECONDS FROM GV1);
```
This statement returns the integer value 6123456.
Assume the date global variable GV2 has the value '2013-02-14'.
```
  VALUES EXTRACT(DECADE FROM GV2);
```
This statement returns the integer value 201.
Assume the decimal(6,0) global variable GV3 has the value 123020.
```
  VALUES EXTRACT(SECONDS FROM GV3);
```
This statement returns the integer value 20.