• Article

DATEDIFF (Report Builder 1.0)

Returns the difference between the Start datetime or time and the End datetime or time field types. The result is displayed as an integer.

Syntax

DATEDIFF(interval, datetime, datetime)

interval

Specifies the units (year, quarter, month, day, hour, minute, second or week) used to calculate the date difference.

datetime

Specifies the start datetime or time.

datetime

Specifies the end datetime or time.

Remarks

  • If the formula calculates the difference in a start time and an end time, the interval unit may only be HOUR, MINUTE, or SECOND.

  • To display a datetime value as a time value, you must format the field to use one of the following custom datetime format strings on the Number tab of the Format dialog box: u, f, or s.

  • Intervals are not case-sensitive.

The following intervals are valid.

Interval

Results

YEAR

Returns the number of years between the start and end datetime or time values.

QUARTER

Returns the number of quarters between the start and end datetime or time values.

MONTH

Returns the number of months between the start and end datetime or time values.

WEEK

Returns the number of weeks between the start and end datetime or time values.

DAY

Returns the number of days between the start and end datetime or time values.

HOUR

Returns the number of hours between the start and end datetime or time values.

MINUTE

Returns the number of minutes between the start and end datetime or time values.

SECOND

Returns the number of seconds between the start and end datetime or time values.

Example

Formula

Result

DATEDIFF(MONTH, #1/1/2009#, #3/31/2009#)

2

DATEDIFF(HOUR, #7/15/2006 3:30:29 AM#, #7/20/2006 5:30:29 AM#)

122

DATEDIFF(DAY, Sell Start Date, Sell End Date)

Returns the number of days difference for each instance of the Sell Start Date and Sell End Date fields.