The $CALC_DATE function performs arithmetic on a date expression, returning a new date value. The arithmetic is performed using the units declared in the function, or using DAYS if not specified.
This function returns the same datatype as the passed date expression.
$CALC_DATE( date_expression, numeric_expression [ , 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.
Required. Any expression that returns a numeric-class datatype. Examples of numeric expressions are columns, functions or expressions that return a numeric-class datatype. The numeric datatypes are found in the list of Omnidex datatypes.
If this function is to return a date in the future, the numeric expression should return a positive value. If this function is to return a date in the past, the numeric expression should return a negative value.
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) |
Required. A column that is declared as a 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, $calc_date(birthdate, 18, 'YY') bday_plus_18 from individuals; BIRTHDATE BDAY_PLUS_18 ------------ ------------ 1938-07-14 1956-07-14 1957-11-22 1975-11-22 1960-07-28 1978-07-28 1929-08-22 1947-08-22 1933-07-09 1951-07-09 1944-08-01 1962-08-01 2004-09-14 2022-09-14
See also: