DATEDIFF

Returns the count of interval boundaries crossed between two dates.

Syntax

DATEDIFF(<start_date>, <end_date>, <interval>)  

Parameters

Term Definition
start_date A scalar datetime value.
end_date A scalar datetime value Return value.
interval The interval to use when comparing dates. The value can be one of the following:

- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR

Return value

The count of interval boundaries crossed between two dates.

Remarks

An error is returned if start_date is larger than end_date.

Example

Date
2012-12-31 23:59:59
2013-01-01 00:00:00

The following all return 1:

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], second ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], minute ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], hour ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], day ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], week ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], month ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], quarter ) )  
  
DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date], year ) )