Omnidex SQL Function: $COMPARE_DATES


The $COMPARE_DATES function compares two date expression and returns the difference. The arithmetic is performed using the units declared in the function, or using days if not specified.

This function returns an INTEGER datatype.


$COMPARE_DATE( date_expression_1, date_expression_2 [ , date_token ] )
date_expression_1 and date_expression_2

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.


Optional. One of the following tokens representing the unit for the date arithmetic.

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)


Example 1: $COMPARE_DATES calculating age

> select birthdate, $compare_dates(birthdate, current_date, 'YY') age from individuals;

------------  -----------
  1938-07-14           72
  1957-11-22           53
  1960-07-28           50
  1929-08-22           81
  1933-07-09           77
  1944-08-01           66
  2004-09-14            6

Additional Resources

See also:

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