| D toc |
|---|
| 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 s | ||
|---|---|---|
|
| D code |
|---|
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.
| D s | ||
|---|---|---|
|
| D code |
|---|
derive type:single value: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 |
| D s lang notes |
|---|
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
date1anddate2have a specified time zone offset,DATEDIFcalculates the difference including the timezone offsets. - If
date1does not have a specified time zone butdate2does,DATEDIFuses the local time in the same time zone asdate2to calculate the difference.DATEDIFreturns the difference without the time zone offset.
D s snippet usage
| Required? | Data Type | Example Value |
|---|---|---|
| Yes | String (Date column reference) | LastContactDate |
date_units
Unit of date measurement to calculate between the two valid dates.
| D s | ||
|---|---|---|
|
| Required? | Data Type | Example Value |
|---|---|---|
| Yes | String | year |
Accepted Value for date units:
yearquarter- month
dayofyearweekdayhourminutesecondmillisecond
| D s | ||
|---|---|---|
|
Example - aged orders
| Include Page | ||||
|---|---|---|---|---|
|
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 |
Transform:
In this case, the transform is simple:
| D code |
|---|
derive type:single value:DATEDIF(d1,d2,dayofyear) as:'datedifs' |
| 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
dayofyearthe result is the same as for Row 1.Info NOTE: When computing
dayofyear, the year value ford2is converted to the year ofd1. The difference is then computed.Row 3 represents the reversal of dates in Row 2.
Info NOTE: Negative values for a
dayofyearcalculation indicate thatd2occurs earlier in the calendar thand1, ignoring year.
Rows 4 - 7: Leap years
- Row 4 provides a baseline calculation for a non-leap 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
DATEDIFresult is 1 more than the value in the previous row.Info NOTE: When the two dates span a leap date and the year for
d1is 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,d2is converted to the year ofd1. However, since it was a leap day originally, in the year ofd1, this value is March 1. Thus, the difference between the two dates is1.Info NOTE: If
d2is a leap date and the year ford1is not a leap year, the date used in ford2is March 1 in the year ofd1.
| D s also | ||
|---|---|---|
|
