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
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