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 | Month |
| DAY | INTEGER | Day |
| HOUR | Hour | |
| MINUTE | Minute | |
| SECOND | Second | |
| A | Lowercase am/pm indicator | |
| AA | Uppercase AM/PM indicator | |
| D | Non-zero-filled day-of-month (1, 2, 3) | |
| 0D | Zero-filled day-of-month (01, 02, 03) | |
| DD | Day of year (44) | |
| 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
odxenv.xyz
odxlogs\
odxsql_history
odxsql_settings
sql_functions.sql
table1
table1.sql
table1_inserts.sql
test.out
test1.xml
test2
test2.out
tiny.dsn
tiny.src
tiny.xml
tiny_create.sql
use.txt
29 files in 1 directory 78,353 bytes
> view extract_describe.sql
select extract(YEAR from current_date) from $omnidex;
describe
select extract(MONTH from current_date) from $omnidex;
describe
select extract(DAY from current_date) from $omnidex;
describe
select extract(HOUR from current_date) from $omnidex;
describe
select extract(MINUTE from current_date) from $omnidex;
describe
select extract(SECOND from current_date) from $omnidex;
describe
select extract(A from current_date) from $omnidex;
describe
select extract(AA from current_date) from $omnidex;
describe
select extract(D from current_date) from $omnidex;
describe
select extract(0D from current_date) from $omnidex;
describe
select extract(DD from current_date) from $omnidex;
describe
select extract(0DD from current_date) from $omnidex;
describe
select extract(F from current_date) from $omnidex;
describe
select extract(0F from current_date) from $omnidex;
describe
select extract(H from current_date) from $omnidex;
describe
select extract(0H from current_date) from $omnidex;
describe
select extract(HH from current_date) from $omnidex;
describe
select extract(0HH from current_date) from $omnidex;
describe
select extract(J from current_date) from $omnidex;
describe
select extract(0J from current_date) from $omnidex;
describe
select extract(M from current_date) from $omnidex;
describe
select extract(0M from current_date) from $omnidex;
describe
select extract(MM from current_date) from $omnidex;
describe
select extract(MMM from current_date) from $omnidex;
describe
select extract(N from current_date) from $omnidex;
describe
select extract(0N from current_date) from $omnidex;
describe
select extract(Q from current_date) from $omnidex;
describe
select extract(QQ from current_date) from $omnidex;
describe
select extract(QQQ from current_date) from $omnidex;
describe
select extract(S from current_date) from $omnidex;
describe
select extract(0S from current_date) from $omnidex;
describe
select extract(W from current_date) from $omnidex;
describe
select extract(WW from current_date) from $omnidex;
describe
select extract(WWW from current_date) from $omnidex;
describe
select extract(YY from current_date) from $omnidex;
describe
select extract(YYYY from current_date) from $omnidex;
describe
------------------------------- End Of File --------------------------------
> dir
.odxsql_history
create_tiny.sql
datetest
DB10000
DB10001
DB1STATS
extract.sql
extract_describe.sql
insert_datetest.sql
mytest.xml
odbctrace
odxdebug.log
odxenv.xml
odxenv.xyz
odxlogs\
odxsql_history
odxsql_settings
sql_functions.sql
table1
table1.sql
table1_inserts.sql
test.out
test1.xml
test2
test2.out
tiny.dsn
tiny.src
tiny.xml
tiny_create.sql
use.txt
29 files in 1 directory 78,385 bytes
> edit extract_describe.sql
> 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
------------------------------------------------------------------------------
>