8.9.4 EXTRACT

(1) Specification format

scalar-function-EXTRACT ::= EXTRACT(extraction-part FROM source-data)
 
  extraction-part ::= {YEAR|MONTH|DAY|HOUR|MINUTE|SECOND}
  source-data ::= value-expression

To Page Top

(2) Explanation of specification format

extraction-part:

Specifies the part to be extracted from the source data. Specify one of the values listed below. Note that HOUR, MINUTE, and SECOND can be specified only when the source data contains data that represents time.

• YEAR

  Specify this to extract the year part from the source data. The range of values of the execution result is 1 to 9999.

• MONTH

  Specify this to extract the month part from the source data. The range of values of the execution result is 1 to 12.

• DAY

  Specify this to extract the day part from the source data. The range of values of the execution result is 1 to 31.

• HOUR

  Specify this to extract the hour part from the source data. The range of values of the execution result is 0 to 23.

• MINUTE

  Specify this to extract the minute part from the source data. The range of values of the execution result is 0 to 59.

• SECOND

  Specify this to extract the second part from the source data. The range of values of the execution result varies depending on the fractional seconds precision of the source data, as shown in the following table.

  Table 8‒26: Range of values of the execution result of the scalar function EXTRACT (when SECOND is specified as the extraction part)

  Fractional seconds precision of the source data	Range of values of the execution result
  0	0 to 59
  3	0.000 to 59.999
  6	0.000000 to 59.999999
  9	0.000000000 to 59.999999999
  12	0.000000000000 to 59.999999999999

source-data:

Specifies the source data to be extracted from.

The following rules apply:

• Specify the source data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• If you specify YEAR, MONTH, or DAY for extraction-part, the data type of source-data must be DATE, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation of a date or time stamp. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

• If you specify HOUR, MINUTE, or SECOND for extraction-part, the data type of source-data must be TIME, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation of a time or time stamp. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

• You cannot specify a dynamic parameter by itself for the source data.

The following example illustrates the result of executing the scalar function EXTRACT.

Example

Extract the year part of from the DATE type data DATE'2012-03-15'.

EXTRACT(YEAR FROM DATE'2012-03-15') → 2012

To Page Top

(3) Rules

1. If you specify anything other than SECOND for extraction-part, the data type of the execution result will be INTEGER.

2. If you specify SECOND for extraction-part, the data type of the execution result varies depending on the fractional seconds precision of the source data, as shown in the following table.

   Table 8‒27: Data type of the execution result of the scalar function EXTRACT (when SECOND is specified as the extraction part)

   Fractional seconds precision of the source data	Data type of the execution result
   0	INTEGER
   3	DECIMAL(5,3)
   6	DECIMAL(8,6)
   9	DECIMAL(11,9)
   12	DECIMAL(14,12)

3. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

4. If the source data has a null value, the execution result will be a null value.

To Page Top

(4) Examples

Example 1:

Retrieve data from table T1 where the year in column C2 is 2012.

SELECT "C1","C2" FROM "T1"
    WHERE EXTRACT(YEAR FROM "C2")=2012

Example 2:

Delete all of the rows from table T1 where the month in column C2 is not March.

DELETE FROM "T1"
    WHERE EXTRACT(MONTH FROM "C2")<>3

To Page Top