EXTRACT scalar function

The EXTRACT function returns a portion of a datetime based on its arguments.

Extract date values

Read syntax diagramSkip visual syntax diagramEXTRACT( EPOCHMILLENNIUMMILLENNIUMSCENTURYCENTURIESDECADEDECADESYEARYEARSQUARTERMONTHWEEKDAYDAYSDOWDOY FROM date-expressiontimestamp-expression )

Extract time values

Read syntax diagramSkip visual syntax diagramEXTRACT( HOURHOURSMINUTEMINUTESSECONDSECONDSMILLISECONDMILLISECONDSMICROSECONDMICROSECONDS FROM time-expressiontimestamp-expression )

The schema is SYSIBM.

If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Extract date values

EPOCH
The number of seconds since 1970-01-01 00:00:00.00 for the specified date or timestamp expression is returned. The value is positive if the expression represents a date or timestamp that is after 1970-01-01 00:00:00.00; the value is negative for a date or timestamp that is before 1970-01-01 00:00:00.00.
MILLENNIUM or MILLENNIUMS
The ordinal number of the full 1000-year period of the specified date or timestamp expression is returned; for example, 2 for a date between 01 Jan 2000 and 31 Dec 2999
CENTURY or CENTURIES
The ordinal number of the full 100-year period of the specified date or timestamp expression is returned; for example, 20 for a date between 01 Jan 2000 and 31 Dec 2099. Not to be confused with the ordinal system that counts dates up to the year 100 as being in the "first century", dates between 01 Jan 2000 and 31 Dec 2099 as being in the "21st century", etc.
DECADE or DECADES
The ordinal number of the full 10-year period of the specified date or timestamp expression is returned; for example, 201 for a date between 01 Jan 2010 and 31 Dec 2019.
YEAR or YEARS
The years portion of the specified date or timestamp expression is returned. The result is identical to that returned by the YEAR scalar function.
QUARTER
The quarter (1, 2, 3, or 4) of the year of the specified date or timestamp expression is returned.
MONTH
The number (1 - 12) of the month of the specified date or timestamp expression is returned. The result is identical to that returned by the MONTH scalar function.
WEEK
The number (1 - 53) of the week of the year of the specified date or timestamp expression is returned. The value uses the ISO-8601 definition of a week, which begins on Monday; as a result, some years might have 53 weeks, and sometimes the first few days of January can be included as part of the 52nd or 53rd week of the previous year.
DAY or DAYS
The number (1 - 31) of the day of the specified date or timestamp expression is returned. The result is identical to that returned by the DAY scalar function.
DOW
A number (1 for Sunday, 2 for Monday, …, 7 for Saturday) indicating the day of the week of the specified date or timestamp expression is returned.
DOY
A number (1 - 366) indicating the day of the year of the specified date or timestamp expression is returned.
date-expression
An expression that returns the value of either a built-in DATE or built-in character string data type.

If a date expression is a character string, it must be a valid string representation of a date that is not a CLOB. In a Unicode database, if a date expression is a graphic string, it is first converted to a character string before the function is executed.

timestamp-expression
An expression that returns the value of either a built-in TIMESTAMP or built-in character string data type.

If a timestamp expression is a character string, it must be a valid string representation of a timestamp that is not a CLOB. In a Unicode database, if a timestamp expression is a graphic string, it is first converted to a character string before the function is executed.

Extract time values

HOUR or HOURS
The hours portion of the specified time or timestamp expression is returned. The result is identical to that returned by the HOUR scalar function.
MINUTE or MINUTES
The minutes portion of the specified time or timestamp expression is returned. The result is identical to that returned by the MINUTE scalar function.
SECOND or SECONDS
The seconds portion of the specified time or timestamp expression is returned. The result is identical to that returned by:
  • SECOND(expression, 6) when the data type of the expression is a TIME value or a string representation of a TIME or TIMESTAMP
  • SECOND(expression, s) when the data type of the expression is a TIMESTAMP(s) value
MILLISECOND or MILLISECONDS
The seconds portion of the specified timestamp expression, including fractional parts to one thousandth of a second, multiplied by 1000 (0 - 59999) is returned.
MICROSECOND or MICROSECONDS
The seconds portion of the specified timestamp expression, including fractional parts to one millionth of a second, multiplied by 1000000 (0 - 59999999) is returned.
time-expression
An expression that returns the value of either a built-in TIME or built-in character string data type.

If a time expression is a character string, it must be a valid string representation of a time that is not a CLOB. In a Unicode database, if a time expression is a graphic string, it is first converted to a character string before the function is executed.

timestamp-expression
An expression that returns the value of a built-in DATE, TIMESTAMP or character string data type.

If timestamp-expression is a DATE, it is converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).

If a timestamp expression is a character string, it must be a valid string representation of a timestamp or date that is not a CLOB. In a Unicode database, if a timestamp expression is a graphic string, it is first converted to a character string before the function is executed. The string is converted to a TIMESTAMP(6) value.

The data type of the result of the function depends on the part of the datetime value that is specified:
  • If MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, DOW, DOY, HOUR, or MINUTE 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 MILLISECOND, MILLISECONDS, MICROSECOND, or MICROSECONDS is specified with a TIMESTAMP(p) value, the data type of the result is INTEGER.
  • If EPOCH is specified, the data type of result is BIGINT.

If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Examples

In a table with the name PROJECT:
  • Column col1 contains the date value '1988-12-25'. The following statement returns the integer value 12:
       SELECT EXTRACT(MONTH FROM col1) FROM PROJECT;
  • Column col2 contains the timestamp value '2007-02-14 12:15:06.123456'. The following statement returns the integer value 6123:
       SELECT EXTRACT(MILLISECONDS FROM col2) FROM PROJECT;
    The following statement returns the integer value 6123456:
       SELECT EXTRACT(MICROSECONDS FROM col2) FROM PROJECT;
  • Column col3 contains the date value '2013-02-14'. The following statement returns the integer value 201:
       SELECT EXTRACT(DECADE FROM col3) FROM PROJECT;