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.

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 ) |

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 |

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,`DATEDIF`

calculates the difference including the timezone offsets. - If
`date1`

does not have a specified time zone but`date2`

does,`DATEDIF`

uses the local time in the same time zone as`date2`

to calculate the difference.`DATEDIF`

returns the difference without the time zone offset.

Required? | Data Type | Example Value |
---|---|---|

Yes | String (Date column reference) | `LastContactDate` |

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`

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:

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

`dayofyear`

the result is the same as for Row 1.**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.

**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 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

`DATEDIF`

result is 1 more than the value in the previous row.**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`

.**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`

.