This is an old revision of the document!
The EXTRACT function extracts an individual field value from a specified dates datatype. This can be used to extract the day, month, year or any of several other values from a datatype.
This function returns a different datatype based on the requested field value.
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 from 1 to 12. |
| DAY | INTEGER | Returns numeric Day of the Month. |
| HOUR | INTEGER | Returns numeric Hour. |
| MINUTE | INTEGER | Returns numeric Minute. |
| SECOND | INTEGER | Returns numeric Second. |
| A | C STRING(2) | Returns Lowercase am/pm indicator. |
| AA | C STRING(2) | Uppercase AM/PM indicator. |
| D | INTEGER | Returns numeric Day of the Month (1 to 31). |
| 0D | C STRING(2) | Zero-filled day-of-month (01, 02, 03) |
| DD | INTEGER | Day of year in one, two or three digit format. |
| 0DD | C STRING(3) | Zero-filled day of year (044) |
| F | INTEGER | Non-zero-filled fraction of a second (1, 2) |
| 0F | C STRING(2) | Zero-filled fraction of a second (01, 02) |
| H | INTEGER | 12-hour, non-zero-filled hour of day (12, 1) |
| 0H | C STRING(2) | 12-hour, zero-filled hour of day (12, 01) |
| HH | INTEGER | 24-hour, non-zero-filled hour of day (24, 1) |
| 0HH | C STRINg(2) | 24-hour, zero-filled hour of day (24, 01) |
| J | DOUBLE | Non-zero-filled Julian date (1, 2) |
| 0J | C STRING(11) | Zero-filled Julian date (01, 02) |
| M | INTEGER | Non-zero-filled month number (1-January, 2-February) |
| 0M | C STRING(2) | Zero-filled day-of-month number (01-January, 02-February) |
| MM | C STRING(3) | Three-character month abbreviation (Jan, Feb) |
| MMM | C STRING(varies) | Fully spelled month (January, February) |
| N | INTEGER | Non-zero-filled minute of hour (1, 2) |
| 0N | C STRING(2) | Zero-filled minute of hour (01, 02) |
| Q | INTEGER | Quarter of the year (1) |
| C STRING(2) | Quarter of the year abbreviation (Q1) | |
| QQQ | C STRING(varies) | Quarter of the year name (First Quarter) |
| S | INTEGER | Non-zero-filled second of minute (1, 2) |
| 0S | C STRING(2) | Zero-filled second of minute (01, 02) |
| W | INTEGER | Day of the week number (1-Sunday, 2-Monday) |
| WW | C STRING(3) | Three-character day-of-week abbreviation (Sun, Mon) |
| WWW | C STRING(varies) | Fully spelled day-of-week (Sunday, Monday) |
| YY | C STRING(2) | Two-digit year (99, 00) |
| YYYY | INTEGER | Four-digit year (1999, 2000) |
Required. A column of date_class datatype. The date_class datatypes are any of the date or time oriented datatypes found in the list of Omnidex datatypes.
> select birthdate, extract(month from birthdate) mon from individuals; BIRTHDATE MON ------------ ----------- 1938-07-14 7 1957-11-22 11 1960-07-28 7 1929-08-22 8 1933-07-09 7 1944-08-01 8 2004-09-14 9
> select $random(12345) rnd from $omnidex; RND ----------- 1255939488
See also:
###
select status, extract(mmm FROM orders.order_date) from orders where product_no='PRN4356' ORDR JANUARY ORDR DECEMBER CNCL MARCH