DATEADD Function
Add a specified number of units to a valid date. Units can be any supported Datetime unit (e.g. minute, month, year, etc.). Input must be a column reference containing dates.
Note
If this function computes values out of the supported range of dates, the values are written as mismatched values, and the column is likely to be typed as a Datetime column.
Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Basic Usage
dateadd(myDate, 3, month)
Output: Returns the values in the myDate column with three months added to them.
Note
Output dates are always formatted with dashes. For example, if the input values include 12/31/2016, a dateadd function output might be 03-31-2017.
Syntax and Arguments
dateadd(date,delta,date_units)
Argument | Required? | Data Type | Description |
|---|---|---|---|
date | Y | datetime | Starting date to compare |
delta | Y | integer | Number of units to apply to the date value. |
date_units | Y | string | String literal representing the date units to use in the comparison |
For more information on syntax standards, see Language Documentation Syntax Notes.
date
Date values to which to add. It must be a column reference.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String (Date column reference) | LastContactDate |
delta
Number of units to apply to the date values.
Negative integer values are accepted.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | Integer | -3 |
date_units
Unit of date measurement to which to apply the delta value.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String | year |
Accepted Value for date units:
yearmonthweekday
hourminutesecondmillisecond
Examples
Astuce
For additional examples, see Common Tasks.
Example - DATEADD Function
Source:
Here are some example invoices and their dates. You want to calculate the 90-day and 180-day limits, at which point interest is charged.
InvNum | InvDate |
|---|---|
inv0001 | 1/1/2016 |
inv0002 | 7/15/2016 |
inv0003 | 12/30/2016 |
Transformation:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | dateadd(InvDate,90,day) |
Parameter: New column name | 'plus90' |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | dateadd(InvDate,180,day) |
Parameter: New column name | 'plus180' |
Results:
Note
The output format is always formatted with dashes.
InvNum | InvDate | plus90 | plus180 |
|---|---|---|---|
inv0001 | 1/1/2016 | 3-31-2016 | 6-29-2016 |
inv0002 | 7/15/2016 | 10-13-2016 | 1-11-2017 |
inv0003 | 12/30/2016 | 3-30-2017 | 6-28-2017 |