EXTRACT
The EXTRACT function returns a specified portion of a datetime value.
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,
time-duration, or timestamp-duration
is returned. Returns 0 for a date-expression. 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,
time-duration, or timestamp-duration
is returned. Returns 0 for a date-expression. 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,
time-duration, or timestamp-duration
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, 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
.
- SECOND(expression, 6) when the data type of expression is a TIME value, a string representation
of a TIME or TIMESTAMP,
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, or time-duration.
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, or time-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. 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.
time-duration
A time duration expressed as a DECIMAL(6,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.
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.
This statement returns the integer value 12.SELECT EXTRACT( MONTH FROM PRSTDATE ) FROM PROJECT
Assume the timestamp global variable GV1 has the value '2007-02-14 12:15:06.123456'.
This statement returns the integer value 6123.VALUES EXTRACT(MILLISECONDS FROM GV1);
Assume the timestamp global variable GV1 has the value '2007-02-14 12:15:06.123456'.
This statement returns the integer value 6123456.VALUES EXTRACT(MICROSECONDS FROM GV1);
Assume the date global variable GV2 has the value '2013-02-14'.
This statement returns the integer value 201.VALUES EXTRACT(DECADE FROM GV2);
Assume the decimal(6,0) global variable GV3 has the value 123020.
This statement returns the integer value 20.VALUES EXTRACT(SECONDS FROM GV3);