Differences

This shows you the differences between two versions of the page.

--- dev:sql:functions:extract [2010/07/26 21:12]
els
+++ dev:sql:functions:extract [2016/06/28 22:38] (current)
@@ Line 3: / Line 3: @@
 ====== Omnidex SQL Function: EXTRACT ======
 ===== Description =====
-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.
+The EXTRACT function extracts one or more portions of a date, such as a day, month or year.   This is useful for performing logic on portions of a date, such as performing certain actions in certain months.  This is also useful for assembling a date string in a specific format.
 This function returns a different datatype based on the requested field value.
 ===== Syntax =====
-  EXTRACT(date_unit FROM date_column)
+  EXTRACT(date_tokens FROM date_expression)
-== date_unit ==
+== date_tokens ==
-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.
+Required. One or more tokens representing the portions of date to be extracted, as determined by one or more of the strings shown in the table below.  //Date_tokens// may also contain punctuation marks to create simple format string for dates, as shown in the examples below.  When punctuation marks are used, //date_tokens// should be enclosed in single quotes.
-The //date_unit// should not be enclosed in either double or single quotes.  The values can be entered in either uppercase or lowercase characters.
+{{page>dev:appendix:reference:date_tokens_insert&noheader&nofooter&noeditbtn}}
-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_expression ==
-^ 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) |
-| QQ     | 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) |
-== date_column ==
+Required. Any expression that returns a date-class datatype. Examples of date expressions are columns or functions that return a date-class datatype.  The date_class datatypes are any of the date or time oriented datatypes found in the list of [[dev:appendix:reference:datatypes|Omnidex datatypes]].
-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 [[dev:appendix:datatypes|Omnidex datatypes]].
@@ Line 79: / Line 38: @@
 -09-14            9
 </code>
+==== Example 2: EXTRACT with format string ====
-==== Example 2: $RANDOM with a seed ====
 <code>
-> select $random(12345) rnd from $omnidex;
+> select birthdate, extract ('MMM D, YYYY' from birthdate) bday from individuals;
-RND
+BIRTHDATE     BDAY
------------
+------------  -------------------
- 1255939488
+-07-14  July 14, 1938
+-11-22  November 22, 1957
+-07-28  July 28, 1960
+-08-22  August 22, 1929
+-07-09  July 9, 1933
+-08-01  August 1, 1944
+-09-14  September 14, 2004
 </code>
@@ Line 93: / Line 57: @@
 See also:
-{{page>dev:sql:functions:see_also_date&noheader&nofooter&noeditbtn}}
+{{page>dev:sql:functions:see_also_dates&noheader&nofooter&noeditbtn}}
-{{page>:bottom_add&nofooter&noeditbtn}}
-###
-===== Example =====
-<code>
-select status,
-extract(mmm FROM orders.order_date)
-from orders
-where product_no='PRN4356'
-ORDR   JANUARY
-ORDR   DECEMBER
-CNCL   MARCH
-</code>
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/functions/extract.1280178775.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes