dateadd function

  • Article

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

Adds value units to a timestamp expr. This function is a synonym for timestampadd function.

Syntax

dateadd(unit, value, expr)

unit
 { MICROSECOND |
   MILLISECOND |
   SECOND |
   MINUTE |
   HOUR |
   DAY | DAYOFYEAR |
   WEEK |
   MONTH |
   QUARTER |
   YEAR }

Arguments

  • unit: A unit of measure.
  • value: A numeric expression with the number of units to add to expr.
  • expr: A TIMESTAMP expression.

Returns

A TIMESTAMP.

If value is negative it is subtracted from the expr. If unit is MONTH, QUARTER, or YEAR the day portion of the result will be adjusted to result in a valid date.

The function returns an overflow error if the result is beyond the supported range of timestamps.

Examples

> SELECT dateadd(MICROSECOND, 5, TIMESTAMP'2022-02-28 00:00:00');
 2022-02-28 00:00:00.000005

-- March 31. 2022 minus 1 month yields February 28. 2022
> SELECT dateadd(MONTH, -1, TIMESTAMP'2022-03-31 00:00:00');
 2022-02-28 00:00:00.000000