**Contents:**

**The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.**

**Contents:**

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.

## Basic Usage

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`

.

## Syntax

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 |

For more information on syntax standards, see Language Documentation Syntax 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
`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.

**Usage Notes:**

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

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

### date_units

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

**Usage Notes:**

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

Yes | String | `year` |

**Accepted Value for date units:**

`year`

`quarter`

`month`

`dayofyear`

`week`

`day`

`hour`

`minute`

`second`

`millisecond`

## Examples

### Example - aged orders

This example illustrates how to use the `DATEDIF`

function to calculate the number of days that have elapsed between the order date and today for purposes of informing the customer.

**Source:**

For the orders in the following set, you want to charge interest for those ones that are older than 90 days.

OrderId | OrderDate | Amount |
---|---|---|

1001 | 1/31/16 | 1000 |

1002 | 11/15/15 | 1000 |

1003 | 12/18/15 | 1000 |

1004 | 1/15/16 | 1000 |

**Transform:**

The first step is to create a column containing today's (3/16/16) date value:

derive type:single value:TODAY() as:'Today'

You can now use this value as the basis for computing the number of elapsed days for each invoice:

derive type:single value:DATEDIF(OrderDate, Today, day)

The age of each invoice in days is displayed in the new column. Now, you want to add a little bit of information to this comparison. Instead of just calculating the number of days, you could write out the action to undertake. Replace the above with the following:

derive type:single value:IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') as:'TakeAction'

To be fair to your customers, you might want to issue a notice at 45 days that the invoice is outstanding. You can replace the above with the following:

derive type:single value:IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) as: 'TakeAction'

By using nested instances of the `IF`

function, you can generate multiple results in the `TakeAction`

column.

For the items that are over 90 days old, you want to charge 5% interest. You can do the following:

set col:Amount value:IF(TakeAction == 'Charge interest',Amount * 1.05,Amount)

The above sets the value in the `Amount`

column based on the conditional of whether the `TakeAction`

column value is `Charge interest`

. If so, apply 5% interest to the value in the `Amount`

column.

**Results:**

OrderId | OrderDate | Amount | Today | TakeAction |
---|---|---|---|---|

1001 | 1/31/16 | 1000 | 03/03/16 | no action |

1002 | 11/15/15 | 1050 | 03/03/16 | Charge interest |

1003 | 12/18/15 | 1000 | 03/03/16 | Send letter |

1004 | 1/15/16 | 1000 | 03/03/16 | Send letter |

### 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:

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

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

.

This page has no comments.