Find first and last day for previous calendar month in SQL Server Reporting Services (VB.Net)
Solution 1
Randall, here are the VB expressions I found to work in SSRS to obtain the first and last days of any month, using the current month as a reference:
First day of last month:
=dateadd("m",-1,dateserial(year(Today),month(Today),1))
First day of this month:
=dateadd("m",0,dateserial(year(Today),month(Today),1))
First day of next month:
=dateadd("m",1,dateserial(year(Today),month(Today),1))
Last day of last month:
=dateadd("m",0,dateserial(year(Today),month(Today),0))
Last day of this month:
=dateadd("m",1,dateserial(year(Today),month(Today),0))
Last day of next month:
=dateadd("m",2,dateserial(year(Today),month(Today),0))
The MSDN documentation for the VisualBasic DateSerial(year,month,day)
function explains that the function accepts values outside the expected range for the year
, month
, and day
parameters. This allows you to specify useful date-relative values. For instance, a value of 0 for Day
means "the last day of the preceding month". It makes sense: that's the day before day 1 of the current month.
Solution 2
These functions have been very helpful to me - especially in setting up subscription reports; however, I noticed when using the Last Day of Current Month function posted above, it works as long as the proceeding month has the same number of days as the current month. I have worked through and tested these modifications and hope they help other developers in the future:
Date Formulas: Find the First Day of Previous Month:
DateAdd("m", -1, DateSerial(Year(Today()), Month(Today()), 1))
Find Last Day of Previous Month:
DateSerial(Year(Today()), Month(Today()), 0)
Find First Day of Current Month:
DateSerial(Year(Today()),Month(Today()),1)
Find Last Day of Current Month:
DateSerial(Year(Today()),Month(DateAdd("m", 1, Today())),0)
Solution 3
Dim thisMonth As New DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)
Dim firstDayLastMonth As DateTime
Dim lastDayLastMonth As DateTime
firstDayLastMonth = thisMonth.AddMonths(-1)
lastDayLastMonth = thisMonth.AddDays(-1)
Solution 4
I'm not familiar with SSRS, but you can get the beginning and end of the previous month in VB.Net using the DateTime
constructor, like this:
Dim prevMonth As DateTime = yourDate.AddMonths(-1)
Dim prevMonthStart As New DateTime(prevMonth.Year, prevMonth.Month, 1)
Dim prevMonthEnd As New DateTime(prevMonth.Year, prevMonth.Month, DateTime.DaysInMonth(prevMonth.Year, prevMonth.Month))
(yourDate
can be any DateTime
object, such as DateTime.Today
or #12/23/2003#
)
Solution 5
I was having some difficulty translating actual VB.NET to the Expression subset that SSRS uses. You definitely inspired me though and this is what I came up with.
StartDate
=dateadd("d",0,dateserial(year(dateadd("d",-1,dateserial(year(Today),month(Today),1))),month(dateadd("d",-1,dateserial(year(Today),month(Today),1))),1))
End Date
=dateadd("d",0,dateserial(year(Today),month(Today),1))
I know it's a bit recursive for the StartDate (first day of last month). Is there anything I'm missing here? These are strictly date fields (i.e. no time), but I think this should capture leap year, etc.
How did I do?
Randall
Updated on July 17, 2020Comments
-
Randall almost 4 years
I'm creating a report in MS SQL Server Reporting Services and need to set the default Start and End Date report parameters to be the first and last dates of the previous calendar month and need help.
The report is generated on the 2nd calendar day of the month and I need values for:
Previous Calendar Month
- first day
- last dayI've been working with DateAdd, but have not been successful at creating an Expression (in VB.NET as I understand it). I would really appreciate any help you can give me!
-
Fredrik Mörk over 14 yearsYou can just change
new
toNew
and call it VB.NET -
Fredrik Mörk over 14 years+1 for storing the "reference date" in a variable, which eliminates the (quite unlikely, but possible) condition where there is a month shift between calculating the first and last day.
-
David M over 14 yearsI've not seen this in production, but I have seen a broken scheduled build for exactly this reason. :)
-
stubaker over 9 yearsLast day of this month and last day of next month are incorrect; both assume that months to come have the same number of days as the preceding month. See @Stephanie Grice's answer for a better way to do this in an SSRS expression.