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

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.
A duration cannot be specified (SQLCODE SQL0171, SQLSTATE 22546).
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 or duration 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 or duration 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 or duration 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 or duration 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 or duration is returned.

MONTH

The number (1 - 12) of the month of the specified date or timestamp expression or duration 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 or duration 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 or duration 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.
A duration cannot be specified (SQLCODE SQL0171, SQLSTATE 22546).

DOY

A number (1 - 366) indicating the day of the year of the specified date or timestamp expression is returned.
A duration cannot be specified (SQLCODE SQL0171, SQLSTATE 22546).

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.

date-duration

A DECIMAL(8,0) number that specifies a date duration (see Datetime operations and durations).

timestamp-duration

A DECIMAL(14+s,s) number that specifies a timestamp duration, where s represents the number of digits of fractional seconds ranging from 0 to 12 (see Datetime operations and durations).

Extract time values

HOUR or HOURS

The hours portion of the specified time or timestamp expression or duration 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 or duration 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 or duration is returned.
The result is identical to that returned by:

• SECOND(expression, 6) when the data type of the expression or duration is a TIME value or a string representation of a TIME or TIMESTAMP
• SECOND(expression, s) when the data type of the expression or duration is a TIMESTAMP(s) value

MILLISECOND or MILLISECONDS

The seconds portion of the specified timestamp expression or duration, including fractional parts to one thousandth of a second, multiplied by 1000 (0 - 59999) is returned.
A time expression or duration cannot be specified (SQLCODE SQL0180, SQLSTATE 22007).

MICROSECOND or MICROSECONDS

The seconds portion of the specified timestamp expression or duration, including fractional parts to one millionth of a second, multiplied by 1000000 (0 - 59999999) is returned.
A time expression or duration cannot be specified (SQLCODE SQL0180, SQLSTATE 22007).

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.

time-duration

A DECIMAL(6,0) number that specifies a time duration ( see Datetime operations and durations).

timestamp-duration

A DECIMAL(14+s,s) number that specifies a timestamp duration, where s represents the number of digits of fractional seconds ranging from 0 to 12 ( see Datetime operations and durations).

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

1. Column col1 contains the date value '1988-12-25'.
   The following statement returns the integer value 12:

      SELECT EXTRACT(MONTH FROM col1) FROM PROJECT;

2. 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;

3. Column col3 contains the date value '2013-02-14'.
   The following statement returns the integer value 201:

      SELECT EXTRACT(DECADE FROM col3) FROM PROJECT;

4. Column col4 has a data type of DECIMAL(8,0) and contains the value 12301000.
   Because the data type is DECIMAL(8,0), this value will be interpreted as a date duration (1230 years, 10 months, 00 days). The following statement returns the integer value 10:

      SELECT EXTRACT(MONTH FROM col4) FROM PROJECT;

5. Column col5 has a data type of DECIMAL(6,0) and contains the value 123010.
   Because the data type is DECIMAL(6,0), this value will be interpreted as a time duration (12 hours, 30 minutes, 10 seconds).
   The following statement returns the integer value 10:

      SELECT EXTRACT(SECONDS FROM col5) FROM PROJECT;

6. Column col6 has a data type of DECIMAL(16,2) and contains the value 12301000123010.45.
   Because the data type is DECIMAL(16,2), this value will be interpreted as a timestamp duration (1230 years, 10 months, 00 days, 12 hours, 30 minutes, 10.45 seconds).
   The following statement returns the integer value 10450:

      SELECT EXTRACT(MILLISECOND FROM col6) FROM PROJECT;