try_add function

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

Since: Databricks Runtime 10.0

Syntax

try_add ( expr1 , expr2 )

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 Runtime returns NULL.

When you add a year-month interval to a DATE Databricks Runtime 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