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

Start of changeEPOCHEnd of change
Start of changeSpecifies 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.End of change
Start of changeMILLENNIUM or MILLENNIUMSEnd of change
Start of changeSpecifies 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.End of change
Start of changeCENTURY or CENTURIESEnd of change
Start of changeSpecifies 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.End of change
Start of changeDECADE or DECADESEnd of change
Start of changeSpecifies 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.End of change
YEAR Start of changeor YEARSEnd of change
Specifies that the year portion of the date-expression, timestamp-expression, Start of changedate-duration, or timestamp-durationEnd of change is returned. The result is identical to the YEAR scalar function. For more information, see YEAR.
Start of changeQUARTEREnd of change
Start of changeSpecifies 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.End of change
MONTH Start of changeor MONTHSEnd of change
Specifies that the month portion of the date-expression, timestamp-expression, Start of changedate-duration, or timestamp-durationEnd of change is returned. The result is identical to the MONTH scalar function. For more information, see MONTH.
Start of changeWEEKEnd of change
Start of changeSpecifies 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.End of change
DAY Start of changeor DAYSEnd of change
Specifies that the day portion of the date-expression, timestamp-expression, Start of changedate-duration or timestamp-durationEnd of change is returned. The result is identical to the DAY scalar function. For more information, see DAY.
Start of changeDOWEnd of change
Start of changeSpecifies 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.End of change
Start of changeDOYEnd of change
Start of changeSpecifies 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.End of change
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.

Start of changedate-durationEnd of change
Start of changeA date duration expressed as a DECIMAL(8,0) number. For the valid formats of datetime durations, see Datetime operands and durations.End of change
Start of changetimestamp-durationEnd of change
Start of changeA 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

Extract Time Values

HOUR Start of changeor HOURSEnd of change
Specifies that the hour portion of the time-expression, timestamp-expression, Start of changetime-duration, or timestamp-durationEnd of change is returned. Returns 0 for a date-expression. The result is identical to the HOUR scalar function. For more information, see HOUR.
MINUTE Start of changeor MINUTESEnd of change
Specifies that the minute portion of the time-expression, timestamp-expression, Start of changetime-duration, or timestamp-durationEnd of change is returned. Returns 0 for a date-expression. The result is identical to the MINUTE scalar function. For more information, see MINUTE.
SECOND Start of changeor SECONDSEnd of change
Specifies that the second portion of the time-expression, timestamp-expression, Start of changetime-duration, or timestamp-durationEnd of change 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, Start of changeor a time durationEnd of change.
  • SECOND(expression, s) when the data type of expression is a TIMESTAMP(s) value Start of changeor a timestamp durationEnd of change.
For more information, see SECOND.
Start of changeMILLISECOND or MILLISECONDSEnd of change
Start of changeSpecifies 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.End of change
Start of changeMICROSECOND or MICROSECONDSEnd of change
Start of changeSpecifies 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.End of change
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.

Start of changetime-durationEnd of change
Start of changeA time duration expressed as a DECIMAL(6,0) number. For the valid formats of datetime durations, see Datetime operands and durations.End of change
Start of changetimestamp-durationEnd of change
Start of changeA 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:

  • Start of changeIf EPOCH is specified, the data type of result is BIGINT.End of change
  • If Start of changeMILLENNIUM, CENTURY, DECADEEnd of change, YEAR, Start of changeQUARTEREnd of change, MONTH, Start of changeWEEKEnd of change, DAY, Start of changeDOW, DOYEnd of change, HOUR, MINUTE, Start of changeMILLISECOND, or MICROSECONDEnd of change 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.
  • Start of changeAssume 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.End of change
  • Start of changeAssume 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.End of change
  • Start of changeAssume the date global variable GV2 has the value '2013-02-14'. 
      VALUES EXTRACT(DECADE FROM GV2);
    This statement returns the integer value 201.End of change
  • Start of changeAssume the decimal(6,0) global variable GV3 has the value 123020. 
      VALUES EXTRACT(SECONDS FROM GV3);
    This statement returns the integer value 20.End of change