# date_diff()

Returns the amount of time between two dates. This function is only valid in a `foreach` statement.

## Syntax

`date_diff(datepart,startdate,enddate)`

*datepart* specifies how you want to measure the time interval:

- `year`
- `month`
- `quarter`
- `day`
- `week`
- `hour`
- `minute`
- `second`

*startdate* specifies the start date.

*enddate* specifies the end date.

## Usage

Returns the time difference between two dates in years, months, or days. For example,

`date_diff("year", toDate("31-12-2015", "dd-MM-yyyy"), toDate("1-1-2016", "dd-MM-yyyy"))` returns 1.

If *startdate* is after *enddate*, the difference is returned as a negative number.

You must use `date_diff()` in a `foreach()` statement. You cannot use this function in `group by`, `order by`, or `filter` statements.

The maximum amount of time returned is 9,223,372,036,854,775,807 nanoseconds. This maximum amount of time can be measured in any supported *datepart* value (nanoseconds aren't supported). For example, in days, the maximum amount of time returned is 106,751.99 days (excluding leap seconds).

## Example - How Many Weeks Did Each Opportunity Take to Close?

Use `date_diff()` with *datepart* = `week` to calculate how long, in weeks, it took to close each opportunity.

```nolang
q = load "DTC_Opportunity";
q = foreach q generate date_diff("week", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch) ) as 'Weeks to Close';
q = order q by 'Weeks to Close';
```

## Example - How Long Ago Was an Opportunity Closed?

Use `date_diff()` with *datepart* = `month` to calculate how many months have passed since each opportunity closed. Use `now()` as the end date.

```nolang
q = load "DTC_Opportunity";
q = foreach q generate date_diff("month", toDate(CloseDate_sec_epoch), now() ) as 'Months Since Close';
q = order q by 'Months Since Close';

```

**See Also**

- [daysBetween()](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-functions-date-between.md)
- [now()](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-functions-date-now.md)
- [Calculate How Long Activities Take](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-examples-daysbetween.md)
- [date_diff(datepart, startdate, enddate)](/docs/analytics/bi-dev-guide-saql/guide/bi-saql-functions-timezone-date-diff.md)