DATEDIFF Function

Metric expressions can use the DATEDIFF function to calculate the difference between two timestamp constants in a specific granularity. Its result is an integer that may be positive, negative, or zero. The DATEDIFF function is similar to that used by SQL Server and uses the following syntax:

DATEDIFF(<units>, <start date>, <end date>)

Where:

•	<units> can be any of the following mnemonics: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. The units mnemonic must be uppercase.

•	<start date> and <end date> are timestamp literals or the NOW() function. All date/time components are optional from right-to-left except for year. Example literal values are "2013-11-13 11:03:02.571", "2013-11-13 11:03", and "2013-11-13". Quotes are optional if the value has only a year component.

The two timestamp values are truncated to their <units> precision, similar to the TRUNCATE function. The difference between the two timestamps in then computed in the requested units, producing a numeric result:

•	If the truncated <start date> is less than the truncated <end date>, the result is a positive number.

•	If the truncated <start date> is greater than the truncated <end date>, the result is a negative number.

•	If the two truncated timestamps are the same, the result is zero.

Common practice is to use an <end date> >= <start date> to produce a positive value. Hence, when NOW() is used, it normally appears as the <end date> parameter. Since DATEDIFF produces a constant, it is most useful when combined with other computations, e.g., to divide an average by the number of days in a query date range. For example:

.../Message?m=COUNT(*)/DATEDIFF(DAY, "2013-12-01", NOW())&q=SendDate:["2013-12-01" TO NOW()]

This query counts the number of messages sent between 2013-12-01 and “now”, inclusively, and divides by the number of days between these same dates.