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