EXTRACT

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

Extract Date Values

Read syntax diagramSkip visual syntax diagramEXTRACT( EPOCHMILLENNIUMMILLENNIUMSCENTURYCENTURIESDECADEDECADESYEARYEARSQUARTERMONTHMONTHSWEEKDAYDAYSDOWDOYFROMdate-expressiontimestamp-expressiondate-durationtimestamp-duration )

Extract Time Values

Read syntax diagramSkip visual syntax diagramEXTRACT( HOURHOURSMINUTEMINUTESSECONDSECONDSMILLISECONDMILLISECONDSMICROSECONDMICROSECONDSFROMdate-expressiontime-expressiontimestamp-expressiontime-durationtimestamp-duration )

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.
For more information, see SECOND.
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. 
      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.