8.9.4 EXTRACT
Extracts a part (year, month, day, hour, minute, or second) from data representing the date and time.
- Organization of this subsection
(1) Specification format
scalar-function-EXTRACT ::= EXTRACT(extraction-part FROM source-data) extraction-part ::= {YEAR|MONTH|DAY|HOUR|MINUTE|SECOND} source-data ::= value-expression
(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
(3) Rules
-
If you specify anything other than SECOND for extraction-part, the data type of the execution result will be INTEGER.
-
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)
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If the source data has a null value, the execution result will be a null value.
(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