try_add function

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

Returns the sum of expr1 and expr2, or NULL in case of error.

Syntax

try_add ( expr1 , expr2 )

This function can also be invoked as a window function using the OVER clause.

Arguments

  • expr1: A numeric, DATE, TIMESTAMP, or INTERVAL expression.
  • expr2: If expr1 is a numeric expr2 must be numeric expression, or an INTERVAL otherwise.

Returns

  • If expr1 is a numeric, the common maximum type of the arguments.
  • If expr1 is a DATE and expr2 is a day-time interval the result is a TIMESTAMP.
  • If expr1 and expr2 are year-month intervals the result is a year-month interval of sufficiently wide units to represent the result.
  • If expr1 and expr2 are day-time intervals the result is a day-time interval of sufficiently wide units to represent the result.
  • Otherwise, the result type matches expr1.

If both expressions are interval they must be of the same class.

If the result overflows the result type Databricks SQL returns NULL.

When you add a year-month interval to a DATE Databricks SQL will assure that the resulting date is well formed.

Examples

> SELECT try_add(1, 2);
 3

> SELECT try_add(DATE'2021-03-20', INTERVAL '2' MONTH);
 2021-5-20

> SELECT try_add(TIMESTAMP'2021-03-20 12:15:29', INTERVAL '3' SECOND);
 2021-03-20 12:15:32

> SELECT typeof(try_add(INTERVAL '3' DAY, INTERVAL '2' HOUR));
 interval day to hour

> SELECT try_add(DATE'2021-03-31', INTERVAL '1' MONTH);
 2021-04-30

> SELECT try_add(127Y, 1Y);
 NULL