EXTRACT

The EXTRACT function returns a portion of a date or timestamp, based on its arguments.

Extract date values:


>>-EXTRACT(-+-YEAR--+-FROM-+-date-expression------+-)----------><
            +-MONTH-+      '-timestamp-expression-'     
            '-DAY---'

Extract time values:


>>-EXTRACT(-+-HOUR---+-FROM-+-time-expression------+-)---------><
            +-MINUTE-+      '-timestamp-expression-'     
            '-SECOND-'

Extract time zone values:


>>-EXTRACT(-+-HOUR------------+-FROM-+-date-expression------+-)-><
            +-MINUTE----------+      +-time-expression------+     
            +-SECOND----------+      '-timestamp-expression-'     
            +-TIMEZONE_HOUR---+                                   
            '-TIMEZONE_MINUTE-'

The schema is SYSIBM.

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

Extract date values

YEAR: Specifies that the year portion of date-expression or timestamp-expression is returned. The result is identical to the YEAR scalar function. For more information, see YEAR.
MONTH: Specifies that the month portion of date-expression or timestamp-expression is returned. The result is identical to the MONTH scalar function. For more information, see MONTH.
DAY: Specifies that the day portion of date-expression or timestamp-expression is returned. The result is identical to the DAY scalar function. For more information, see DAY.
date-expression: An expression that returns the value of either a built-in date or built-in character string data type.
If date-expression is a character or graphic string, it must not be a CLOB or DBCLOB and 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 or built-in character string data type.
If timestamp-expression is a character or graphic string, it must not be a CLOB or DBCLOB and 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.

Extract time values

HOUR: Specifies that the hour portion of time-expression or timestamp-expression is returned. The result is identical to the HOUR scalar function. For more information, see HOUR.
MINUTE: Specifies that the minute portion of time-expression or timestamp-expression is returned. The result is identical to the MINUTE scalar function. For more information, see MINUTE.
SECOND: Specifies that the second portion of time-expression or timestamp-expression is returned. The result is identical to the SECOND scalar function where the precision and scale of the result depend on the type of time-expression or timestamp-expression. For more information, see SECOND.
time-expression: An expression that returns the value of either a built-in time or built-in character string data type.
If time-expression is a character or graphic string, it must not be a CLOB or DBCLOB and its value must be a valid 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 or built-in character string data type.
If timestamp-expression is a character or graphic string, it must not be a CLOB or DBCLOB and its value must be a valid string representation of a timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.

Extract time zone values

TIMEZONE_HOUR: Specifies that the hour component of the time zone of the timestamp value is returned. TIMEZONE_HOUR can only be specified if the second argument is a timestamp-expression and the timestamp-expression contains a time zone.
TIMEZONE_MINUTE: Specifies that the minute component of the time zone of the timestamp value is returned. TIMEZONE_MINUTE can only be specified if the second argument is a timestamp-expression and the timestamp-expression contains a time zone.

The values of TIMEZONE_HOUR and TIMEZONE_MINUTE shall either both be non-negative or both be non-positive.

Start of change If the timestamp-expression argument includes a time zone, the result is determined from the UTC representation of the datetime value. End of change

The data type of the result of the function depends on the part of the datetime value that is specified:

The result is INTEGER, if one of the following is specified:
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
- TIMEZONE_HOUR
- TIMEZONE_MINUTE
The result is DECIMAL(2+p, p) where p is the fractional second precision, if SECOND is specified with a TIMESTAMP(p) value.
The result is DECIMAL(8,6), if SECOND is specified with a TIME value or a string representation of a TIME or timestamp. The fractional digits contains fractional seconds.

Example 1:

Assume that the column PRSTDATE has an internal value that is equivalent to 2010-12-25. The following statement returns the value 12:

   SELECT EXTRACT(MONTH FROM PRSTDATE) 
     FROM PROJECT;

Example 2:

Assume that host variable PRSTSZ contains the value 2008-02-29.20.00.000000 -08.30:

	SELECT EXTRACT(HOUR FROM :PRSTSZ) FROM PROJECT;

The SELECT statement returns the value 4, which is the hour of the input datetime value expressed in UTC.

To return the same hour value as expressed in the input, cast the value to TIMESTAMP WITHOUT TIME ZONE before using the EXTRACT function:

SELECT EXTRACT(HOUR FROM CAST (:PRSTSZ AS TIMESTAMP ) ) FROM PROJECT;

The SELECT statement returns the value 20, which is the hour as it was originally expressed as a string in the host variable.

SELECT EXTRACT(TIMEZONE_HOUR FROM :PRSTSZ) FROM PROJECT;

This SELECT statement returns the value -8.

SELECT EXTRACT(TIMEZONE_MINUTE FROM :PRSTSZ) FROM PROJECT;

This SELECT statement returns the value -30.