This is an old revision of the document!
The EXTRACT function extracts an individual field value from a specified date_class data type.
EXTRACT(date_unit FROM date_column)
Required. The date part that is to be extracted. See below for a complete list of date parts that can be used in this parameter. Click to see a list of valid datefield options.
The date_unit should not be enclosed in either double or single quotes. The values can be entered in either uppercase or lowercase characters.
The return datatype of the function varies depending on the type of date_unit request as shown in the following table.
Valid date_unit options:
| Date_unit | Return Datatype | Description |
|---|---|---|
| YEAR | INTEGER | Returns Year in CCYY format. |
| MONTH | INTEGER | Returns numeric Month. |
| DAY | INTEGER | Returns numeric Day of the |
| HOUR | INTEGER | Hour |
| MINUTE | INTEGER | Minute |
| SECOND | INTEGER | Second |
| A | C STRING(2) | Lowercase am/pm indicator |
| AA | C STRING(2) | Uppercase AM/PM indicator |
| D | INTEGER | Non-zero-filled day-of-month (1, 2, 3) |
| 0D | Zero-filled day-of-month (01, 02, 03) | |
| DD | Day of year in one, two or three digit format. | |
| 0DD | Zero-filled day of year (044) | |
| F | Non-zero-filled fraction of a second (1, 2) | |
| 0F | Zero-filled fraction of a second (01, 02) | |
| H | 12-hour, non-zero-filled hour of day (12, 1) | |
| 0H | 12-hour, zero-filled hour of day (12, 01) | |
| HH | 24-hour, non-zero-filled hour of day (24, 1) | |
| 0HH | 24-hour, zero-filled hour of day (24, 01) | |
| J | Non-zero-filled Julian date (1, 2) | |
| 0J | Zero-filled Julian date (01, 02) | |
| M | Non-zero-filled month number (1-January, 2-February) | |
| 0M | Zero-filled day-of-month number (01-January, 02-February) | |
| MM | Three-character month abbreviation (Jan, Feb) | |
| MMM | Fully spelled month (January, February) | |
| N | Non-zero-filled minute of hour (1, 2) | |
| 0N | Zero-filled minute of hour (01, 02) | |
| Q | Quarter of the year (1) | |
| Quarter of the year abbreviation (Q1) | ||
| QQQ | Quarter of the year name (First Quarter) | |
| S | Non-zero-filled second of minute (1, 2) | |
| 0S | Zero-filled second of minute (01, 02) | |
| W | Day of the week number (1-Sunday, 2-Monday) | |
| WW | Three-character day-of-week abbreviation (Sun, Mon) | |
| WWW | Fully spelled day-of-week (Sunday, Monday) | |
| YY | Two-digit year (99, 00) | |
| YYYY | Four-digit year (1999, 2000) |
Required.
Required. A date formatted as any valid SQL92 date_class data type.
If the original date is an OMNIDEX DATE or OMNIDEX DATETIME column, the return data type is C STRING length 32.
select status, extract(mmm FROM orders.order_date) from orders where product_no='PRN4356' ORDR JANUARY ORDR DECEMBER CNCL MARCH
> use extract_describe.sql
-> select extract(YEAR from current_date) from $omnidex;
EXTRACT(YEA
-----------
2010
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT(YEAR FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(MONTH from current_date) from $omnidex;
EXTRACT(MON
-----------
7
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT(MONTH FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(DAY from current_date) from $omnidex;
EXTRACT(DAY
-----------
8
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT(DAY FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(HOUR from current_date) from $omnidex;
EXTRACT(HOU
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT(HOUR FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(MINUTE from current_date) from $omnidex;
EXTRACT(MIN
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT(MINUTE FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(SECOND from current_date) from $omnidex;
EXTRACT(SEC
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT(SECOND FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(A from current_date) from $omnidex;
EX
--
am
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('A' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(AA from current_date) from $omnidex;
EX
--
AM
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('AA' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(D from current_date) from $omnidex;
EXTRACT('D'
-----------
8
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('D' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0D from current_date) from $omnidex;
EX
--
08
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0D' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(DD from current_date) from $omnidex;
EXTRACT('DD
-----------
189
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('DD' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0DD from current_date) from $omnidex;
EXT
---
189
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0DD' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(3)
Request 0: C STRING(3) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(F from current_date) from $omnidex;
EXTRACT('F'
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('F' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> disconnect;
Disconnected
-> select extract(0F from current_date) from $omnidex;
*E* This statement requires a connection to an environment file
>
-> select extract(0F from current_date) from $omnidex;
EX
--
00
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0F' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(H from current_date) from $omnidex;
EXTRACT('H'
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('H' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0H from current_date) from $omnidex;
EX
--
00
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0H' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(HH from current_date) from $omnidex;
EXTRACT('HH
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('HH' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0HH from current_date) from $omnidex;
EX
--
00
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0HH' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(J from current_date) from $omnidex;
EXTRACT('J' FROM CURRENT_DATE)
--------------------------------
2455385.500000
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('J' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: DOUBLE
Request 0: C STRING(32) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0J from current_date) from $omnidex;
EXTRACT('0J
-----------
2455385.500
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0J' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(11)
Request 0: C STRING(11) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(M from current_date) from $omnidex;
EXTRACT('M'
-----------
7
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('M' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0M from current_date) from $omnidex;
EX
--
07
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0M' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(MM from current_date) from $omnidex;
EXT
---
Jul
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('MM' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(3)
Request 0: C STRING(3) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(MMM from current_date) from $omnidex;
EXTR
----
July
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('MMM' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(4)
Request 0: C STRING(4) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(N from current_date) from $omnidex;
EXTRACT('N'
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('N' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0N from current_date) from $omnidex;
EX
--
00
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0N' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(Q from current_date) from $omnidex;
EXTRACT('Q'
-----------
3
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('Q' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(QQ from current_date) from $omnidex;
EX
--
Q3
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('QQ' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(QQQ from current_date) from $omnidex;
EXTRACT('QQQ'
-------------
Third Quarter
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('QQQ' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(13)
Request 0: C STRING(13) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(S from current_date) from $omnidex;
EXTRACT('S'
-----------
0
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('S' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(0S from current_date) from $omnidex;
EX
--
00
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('0S' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(W from current_date) from $omnidex;
EXTRACT('W'
-----------
5
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('W' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
-> select extract(WW from current_date) from $omnidex;
EXT
---
Thu
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('WW' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(3)
Request 0: C STRING(3) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(WWW from current_date) from $omnidex;
EXTRACT(
--------
Thursday
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('WWW' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(8)
Request 0: C STRING(8) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(YY from current_date) from $omnidex;
EX
--
10
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('YY' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: C STRING(2)
Request 0: C STRING(2) TRUN VALUE EXP
------------------------------------------------------------------------------
-> select extract(YYYY from current_date) from $omnidex;
EXTRACT('YY
-----------
2010
1 row returned
-> describe
------------------------------------------------------------------------------
Expr. (1) : EXTRACT('YYYY' FROM CURRENT_DATE)
Occurrences : 1 Length: 016C01D0
Actual 0: INT
Request 0: C STRING(11) VALUE EXP OVRFL
------------------------------------------------------------------------------
>