try_subtract function

  • Article

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

Returns the subtraction of expr2 from expr1, or NULL on overflow.

Syntax

try_subtract ( 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 SQL returns NULL.

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

Examples

> SELECT try_subtract(1, 2);
 -1

> SELECT try_subtract(DATE'2021-03-20', INTERVAL '2' MONTH);
 2021-1-20

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

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

> SELECT try_subtract(DATE'2021-03-31', INTERVAL '1' MONTH);
 2021-02-28

> SELECT try_subtract(-128Y, 1Y);
  NULL