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 ] )
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) |
| 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) |
> select birthdate, $compare_dates(birthdate, current_date, 'YY') age from individuals; BIRTHDATE AGE ------------ ----------- 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
See also: