EXTRACT

Edit online

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.

A timestamp-expression is never converted to UTC when calculating the result.

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.