Skip to main content

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.

Nota

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.

Nota

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:

  • year

  • month

  • week

  • day

  • hour

  • minute

  • second

  • millisecond

Examples

Dica

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

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

dateadd(InvDate,90,day)

Parameter: New column name

'plus90'

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

dateadd(InvDate,180,day)

Parameter: New column name

'plus180'

Results:

Nota

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