Excerpt 

Calculates the difference between two valid date values for the specified units of measure. 
 Inputs must be column references.
 The first value is used as the baseline to compare the date values.
 Results are calculated to the integer value that is closest to and lower than the exact total; remaining decimal values are dropped.
D lang syntax 

RawWrangle  true 

Type  ref 

showNote  true 

WrangleText  derive type:single value:datedif(StartDate, EndDate, month) 


datedif(StartDate, EndDate, month) 
Output: Returns the number of full months that have elapsed between StartDate
and EndDate
.
D lang syntax 

RawWrangle  true 

Type  syntax 

showNote  true 

WrangleText  derive type:single value:datedif(date1,date2,date_units) 


datedif(date1,date2,date_units) 
Argument  Required?  Data Type  Description 

date1  Y  datetime  Starting date to compare 
date2  Y  datetime  Ending date to compare 
date_units  Y  string  String 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.
 Date values must be column references.
 If
date1
and date2
have a specified time zone offset, the function calculates the difference including the timezone offsets.  If
date1
does not have a specified time zone but date2
does, the function uses the local time in the same time zone as date2
to calculate the difference. The functions returns the difference without the time zone offset.
Required?  Data Type  Example Value 

Yes  String (Date column reference)  LastContactDate 
date_units
Unit of date measurement to calculate between the two valid dates.
Required?  Data Type  Example Value 

Yes  String  year 
Accepted Value for date units:
year
quarter
month
dayofyear
week
day
hour
minute
second
millisecond
Example  aged orders
Include Page 

 EXAMPLE  DATEDIF Function 

 EXAMPLE  DATEDIF Function 


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:
dateId  d1  d2  Notes 

1  1/1/10  10/10/10  Same year; no leap year 
2  1/1/10  10/10/11  Different years; no leap year 
3  10/10/11  1/1/10  Reverse dates of previous row 
4  2/28/11  4/1/11  Same year; no leap year; 
5  2/28/12  4/1/12  Same year; leap year; spans leap day 
6  2/29/12  4/1/12  Same year; leap year; d1 = leap day 
7  2/28/11  2/29/12  Diff years; d2 = leap day; converted to March 1 in d1 year 
Transformation:
In this case, the transform is simple:
D trans 

RawWrangle  true 

p03Value  'datedifs' 

Type  step 

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

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  datedif(d1,d2,dayofyear) 

p03Name  New column name 

SearchTerm  New formula 


Results:
dateId  d1  d2  datedifs  Notes 

1  1/1/10  10/10/10  282  Same year; no leap year 
2  1/1/10  10/10/11  282  Different years; no leap year 
3  10/10/11  1/1/10  282  Reverse dates of previous row 
4  2/28/11  4/1/11  32  Same year; no leap year; 
5  2/28/12  4/1/12  33  Same year; leap year; spans leap day 
6  2/29/12  4/1/12  32  Same year; leap year; d1 = leap day 
7  2/28/11  2/29/12  1  Diff years; d2 = leap day; converted to March 1 in d1 year 
Rows 1  3:
 Row 1 provides the baseline calc.
In Row 2, the same days of the year are used, but the year is different by a count of 1. However, since we are computing dayofyear
the result is the same as for Row 1.
Info 

NOTE: When computing dayofyear , the year value for d2 is converted to the year of d1 . The difference is then computed. 
Row 3 represents the reversal of dates in Row 2.
Info 

NOTE: Negative values for a dayofyear calculation indicate that d2 occurs earlier in the calendar than d1 , ignoring year. 
Rows 4  7: Leap years
 Row 4 provides a baseline calculation for a nonleap year.
Row 5 uses the same days of year as Row 4, but the year (2012) is a leap year. Dates span a leap date (February 29). Note that the DATEDIF
result is 1 more than the value in the previous row.
Info 

NOTE: When the two dates span a leap date and the year for d1 is a leap year, then February 29 is included as part of the calculated result. 
 Row 6 moves date 1 forward by one day, so it is now on a leap day date. Result is one less than the previous row, which also spanned leap date.
Row 7 switches the leap date to d2
. In this case, d2
is converted to the year of d1
. However, since it was a leap day originally, in the year of d1
, this value is March 1. Thus, the difference between the two dates is 1
.
Info 

NOTE: If d2 is a leap date and the year for d1 is not a leap year, the date used in for d2 is March 1 in the year of d1 . 