Calculates the difference between two valid date values for the specified units of measure.

derive type:single value:DATEDIF(StartDate, EndDate, month)

Output: Generates a column of values calculating the number of full months that have elapsed between StartDate and EndDate.

derive type:single value:DATEDIF(date1,date2,date_units )

ArgumentRequired?Data TypeDescription
date1YdatetimeStarting date to compare
date2YdatetimeEnding date to compare
date_unitsYstringString literal representing the date units to use in the comparison

date1, date2

Date values to compare using the date_units units. If date2 > date1, then results are positive.

 

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

date_units

Unit of date measurement to calculate between the two valid dates.

Required?Data TypeExample Value
YesStringyear

Accepted Value for date units:

Example - aged orders

Example - dayofyear Calculations

This example demonstrates how dayofyear is calculated using the DATEDIF function, specifically how leap years and leap days are handled. Below, you can see some example dates. The year 2012 was a leap year.

Source:

dateIdd1d2Notes
11/1/1010/10/10Same year; no leap year
21/1/1010/10/11Different years; no leap year
310/10/111/1/10Reverse dates of previous row
42/28/114/1/11Same year; no leap year;
52/28/124/1/12Same year; leap year; spans leap day
62/29/124/1/12Same year; leap year; d1 = leap day
72/28/112/29/12Diff years; d2 = leap day; converted to March 1 in d1 year

Transform:

In this case, the transform is simple:

derive type:single value:DATEDIF(d1,d2,dayofyear) as:'datedifs'

Results:

dateIdd1d2datedifsNotes
11/1/1010/10/10282Same year; no leap year
21/1/1010/10/11282Different years; no leap year
310/10/111/1/10-282Reverse dates of previous row
42/28/114/1/1132Same year; no leap year;
52/28/124/1/1233Same year; leap year; spans leap day
62/29/124/1/1232Same year; leap year; d1 = leap day
72/28/112/29/121Diff years; d2 = leap day; converted to March 1 in d1 year

Rows 1 - 3:

Rows 4 - 7: Leap years