Find first and last day for previous calendar month in SQL Server Reporting Services (VB.Net)

user_566189 0
Viewed 139849

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 day

I'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!

calendar reporting-services date vb.net
9 Answers
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)
user_831090

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#)

user_343970

in C#:

new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1)
new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddDays(-1)
Jimmy_44350

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?

user_5661890 
        Dim aDate As DateTime = #3/1/2008# 'sample date
    Dim StartDate As DateTime = aDate.AddMonths(-1).AddDays(-(aDate.Day - 1))
    Dim EndDate As DateTime = StartDate.AddDays(DateTime.DaysInMonth(StartDate.Year, StartDate.Month) - 1)

    'to access just the date portion
    ' StartDate.Date
    ' EndDate.Date
dogboy_665320
Related