The SSRS DateDiff function is a Date and Time function that calculates the difference between two given date fields (total number of time intervals) and returns the long value. For instance, the total years between the hire date and termination or the total days between the order and ship date. The syntax of the SSRS DateDiff function to find the difference between two given dates is shown below.
=DateDiff(DateInterval.Month, Fields!OrderDate.Value, Fields!ShipDate.Value)
=DateDiff("m", Fields!OrderDate.Value, Fields!ShipDate.Value)
To demonstrate the SSRS DateDiff function, we use the Employee table below, which has 15 records. The image shows the records in the Table report.
SSRS DateDiff function Example
For this, let me add a new column to the right side of the Hire Date column and name it DateDiff. Next, right-click the textbox under Now and choose the Expression. To understand the report, I suggest you refer to the articles on charts, tables, grouping, and format tables in SSRS.
It opens the following expression window to calculate the difference between the two dates. First, the Today function returns the current system date. Next, the SSRS DateDiff function uses the Year as the first argument to find the total difference in the number of years between the current system local date and the HireDate column.
=DateDiff(DateInterval.Year, Fields!HireDate.Value, Today())
Please click the preview tab to see the report. For more functions >> Click Here!
Let me add a new column to demonstrate the possible options in the first arguments. The expression below will show the difference in years, Months, Days, Quarters, weeks, Hours, Minutes, Seconds, etc. To ensure you understand the first arguments, we have joined the names of the return values.
="Year = " & DateDiff("yyyy", Fields!HireDate.Value, Now()) &
" Month = " & DateDiff("m", Fields!HireDate.Value, Now()) &
" Day = " & DateDiff("d", Fields!HireDate.Value, Now()) &
" Quarter = " & DateDiff("q", Fields!HireDate.Value, Now()) &
" Week = " & DateDiff("ww", Fields!HireDate.Value, Now()) &
" Weekday = " & DateDiff("w", Fields!HireDate.Value, Now()) &
" DayofWeek=" & DateDiff("y", Fields!HireDate.Value, Now()) &
" Hour = " & DateDiff("h", Fields!HireDate.Value, Now()) &
" Minute = " & DateDiff("n", Fields!HireDate.Value, Now()) &
" Second = " & DateDiff("s", Fields!HireDate.Value, Now())
Please click the preview tab to see the report.
