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.

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, extract(month from birthdate) mon from individuals;

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

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

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

See also:

$CALC_DATE calculates a new date from a specified date or column_date using a numeric positive or negative offset. The offset defaults to days but can be changed by using the date_unit optional argument.

Result = $calc_date( <date_column | 'date_string' >, offset_expression [, date_unit [, options])

Returns a floating point value.

An expression resulting in date-class or string-class datatype.

An expression resulting in a numeric-class (integer or floating point) value.

Negative values supported.

A string containing unit abbreviation that tell the $calc_date function to add or subtract the number of date_units.

Valid date_unit options:

There are no options currently for this function.

> select $calc_date('20090101', -5) from $omnidex

$CALC_DATE('20090101',
----------------------
            2008-12-27

> select $calc_date('20090101', -5,YEAR) from $omnidex

$CALC_DATE('20090101',
----------------------
            2004-01-01