Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0762

D toc
 

Excerpt

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.

Info

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. For more information on supported date ranges, see Datetime Data Type.

D s lang vs sql

D s
snippetBasic

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:dateadd(myDate, 3, month)

dateadd(myDate, 3, month)

Output: Returns the values in the myDate column with three months added to them. 

Info

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-2016.

 

D s
snippetSyntax

D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:dateadd(date,delta,date_units)

dateadd(date,delta,date_units)


ArgumentRequired?Data TypeDescription
dateYdatetimeStarting date to compare
deltaYintegerNumber of units to apply to the date value.
date_unitsYstringString literal representing the date units to use in the comparison

D s lang notes

date

Date values to which to add. It must be a column reference.

D s
snippetusage

 

Required?Data TypeExample Value
YesString (Date column reference)LastContactDate

delta

Number of units to apply to the date values.

  • Negative integer values are accepted.

D s
snippetusage

Required?Data TypeExample Value
YesInteger-3

date_units

Unit of date measurement to which to apply the delta value.

D s
snippetusage

Required?Data TypeExample Value
YesStringyear

Accepted Value for date units:

  • year
  • month
  • week
  • day

  • hour
  • minute
  • second
  • millisecond

D s
snippetExamples

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.

InvNumInvDate
inv00011/1/2016
inv00027/15/2016
inv000312/30/2016

Transformation:

D trans
RawWrangletrue
p03Value'plus90'
Typestep
WrangleTextderive type:single value:dateadd(InvDate,90,day) as:'plus90'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateadd(InvDate,90,day)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'plus180'
Typestep
WrangleTextderive type:single value:dateadd(InvDate,180,day) as:'plus180'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedateadd(InvDate,180,day)
p03NameNew column name
SearchTermNew formula

Results:

Info

NOTE: The output format is always formatted with dashes.

InvNumInvDateplus90plus180
inv00011/1/20163-31-20166-29-2016
inv00027/15/201610-13-20161-11-2017
inv000312/30/20163-30-20176-28-2017

 

D s also
labeldate