try_subtract
function
Applies to: Databricks SQL Databricks Runtime 10.4 LTS and above
Returns the subtraction of expr2
from expr1
, or NULL
on overflow.
Arguments
expr1
: A numeric, DATE, TIMESTAMP, or INTERVAL expression.expr2
: Ifexpr1
is a numericexpr2
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 andexpr2
is a day-time interval the result is a TIMESTAMP.If
expr1
andexpr2
are year-month intervals the result is a year-month interval of sufficiently wide units to represent the result.If
expr1
andexpr2
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