Omnidex SQL Function: EXTRACT


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.


EXTRACT(date_tokens FROM date_expression)

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.

Date Token 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 STRING(2) Returns Lowercase am/pm indicator.
AA STRING(2) Uppercase AM/PM indicator.
D INTEGER Returns numeric Day of the Month (1 to 31).
0D STRING(2) Zero-filled day-of-month (01, 02, 03)
DD INTEGER Day of year in one, two or three digit format.
0DD STRING(3) Zero-filled day of year (044)
F INTEGER Non-zero-filled fraction of a second (1, 2)
0F STRING(2) Zero-filled fraction of a second (01, 02)
H INTEGER 12-hour, non-zero-filled hour of day (12, 1)
0H 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 STRING(11) Zero-filled Julian date (01, 02)
M INTEGER Non-zero-filled month number (1-January, 2-February)
0M STRING(2) Zero-filled day-of-month number (01-January, 02-February)
MM STRING(3) Three-character month abbreviation (Jan, Feb)
MMM STRING(varies) Fully spelled month (January, February)
N INTEGER Non-zero-filled minute of hour (1, 2)
0N STRING(2) Zero-filled minute of hour (01, 02)
Q INTEGER Quarter of the year (1)
QQ STRING(2) Quarter of the year abbreviation (Q1)
QQQ STRING(varies) Quarter of the year name (First Quarter)
S INTEGER Non-zero-filled second of minute (1, 2)
0S STRING(2) Zero-filled second of minute (01, 02)
W INTEGER Day of the week number (1-Sunday, 2-Monday)
WW STRING(3) Three-character day-of-week abbreviation (Sun, Mon)
WWW STRING(varies) Fully spelled day-of-week (Sunday, Monday)
YY STRING(2) Two-digit year (99, 00)
YYYY INTEGER Four-digit year (1999, 2000)

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 Omnidex datatypes.


Example 1: EXTRACT of MONTH

> select birthdate, extract(month from birthdate) mon from individuals;

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

Example 2: EXTRACT with format string

> select birthdate, extract ('MMM D, YYYY' from birthdate) bday from individuals;

------------  -------------------
  1938-07-14  July 14, 1938
  1957-11-22  November 22, 1957
  1960-07-28  July 28, 1960
  1929-08-22  August 22, 1929
  1933-07-09  July 9, 1933
  1944-08-01  August 1, 1944
  2004-09-14  September 14, 2004

Additional Resources

See also:

Back to top
dev/sql/functions/extract.txt ยท Last modified: 2016/06/28 22:38 (external edit)