The SSRS DateAdd function is a Date and Time function that adds or subtracts the specified time interval from the given date fields and returns the Date value. For instance, it adds three years to the joining date or two days to the order date as the ship date. The syntax of the SSRS DateAdd function to add or subtract the time intervals from the given date is as shown below.
=DateAdd(DateInterval.Year, 3, Fields!HireDate.Value)
=DateAdd("d", 15, Fields!JoiningDate.Value)
To demonstrate the SSRS DateAdd 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 DateAdd. 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 predict future dates. The SSRS DateAdd function uses the Year as the first argument and 9 as the second, adding nine years to the HireDate column.
=DateAdd(DateInterval.Year, 9, Fields!HireDate.Value)
Similarly, let me add a new column to show the subtracting option. The below code will subtract five years from the HireDate column to show the past dates.
=DateAdd("yyyy", -5, Fields!HireDate.Value)
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 SSRS DateAdd Function expression will add 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 = " & DateAdd("yyyy", 5, Fields!HireDate.Value) &
" Month = " & DateAdd("m", 6, Fields!HireDate.Value) &
" Day = " & DateAdd("d", 15, Fields!HireDate.Value) &
" Quarter = " & DateAdd("q", 1, Fields!HireDate.Value) &
" Week = " & DateAdd("ww",2, Fields!HireDate.Value) &
" Weekday = " & DateAdd("w", 5, Fields!HireDate.Value) &
" DayofWeek=" & DateAdd("y", 2, Fields!HireDate.Value) &
" Hour = " & DateAdd("h", 10,Fields!HireDate.Value) &
" Minute = " & DateAdd("n", 200, Fields!HireDate.Value) &
" Second = " & DateAdd("s", 100, Fields!HireDate.Value)
Please click the preview tab to see the report.
