Date Calculations in SSRS

When creating SSRS reports and setting their default parameters for dates, I always am trying to remember what functions to input into the parameter’s default value for the date calculations in SSRS which is either in Visual Studio or Report Builder.

The below table contains a list of some of the common expression I use in the SSRS expression editor for the date calculations in SSRS. We can use native VB functions for different date calculations and we can also use .Net Properties and Methods for doing the calculations. The choice is up to you and is your preference. I hope the table below will help you as it has helped me and if there is anything I am missing be sure to leave a comment!

Date Format VB Functions .Net Functions
First Date of last month =DateAdd(“m”, -1, DateSerial(Year(Now()), Month(Now()), 1)) =Today.AddDays(1- Today.Day).AddMonths(-1)
Last date of last month =DateAdd(“d”, -1, DateSerial(Year(Now()), Month(Now()), 1)) =Today.AddDays(-1.0 * Today.Day)
First date of current month =DateSerial(Year(Now()), Month(Now()), 1) =Today.AddDays(1 – Today.Day)
Last date of current month =DateAdd(“d”,-1,(DateAdd(“m”, 1, DateSerial(Year(Now()), Month(Now()), 1)))) =Today.AddDays(-1 * Today.Day).AddMonths(1)
Yesterday’s date =DateAdd(“d”, -1, Today) =Today.AddDays(-1)
Tomorrow’s date =DateAdd(“d”, 1, Today) =Today.AddDays(1)
First day of current year =DateSerial(Year(Now()), 1, 1) =Today.AddDays(1- Today.DayOfYear)
Last day of current year =DateSerial(Year(Now()), 12, 31) =Today.AddDays(-1 * Today.DayOfYear).AddYears(1)
Last day of current year =DateSerial(Year(Now()), 12, 31) =Today.AddDays(-1 * Today.DayOfYear).AddYears(1)

 

That’s what I have for now! If you have any suggestions let us know by commenting or leaving us a message by filling out the form below and maybe we can find the Date Calculation you are looking for or even help you with your SQL Server Reporting Services.

Your Name:*
Phone:
-
E-mail:*
Website:

Subject:*
Message:*
Type the characters you see here:

About the Author

Samuel Mindlin is a Product Manager and has been with the Mindlin Consulting Group since 2009.

Leave a Reply 0 comments