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

vb.net date reporting-services calendar
Randall picture Randall · Jan 13, 2010 · Viewed 121.4k times · Source

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!

Answer

Ray picture Ray · Sep 25, 2012

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.

Related questions

Difference in months between two dates

How to calculate the difference in months between two dates in C#? Is there is equivalent of VB's DateDiff() method in C#. I need to find difference in months between two dates that are years apart. The documentation says that …

Read More
Convert a string to a datetime

I am developing asp.net site using vb framework 3.5. Im having difficulties converting string data into Date I tried using cdate function, I have a variable sdate which is a string variable and date is stored in it which comes …

Read More
How to compare just the date part and not the time of two Dates?

I want to compare just the date part (and Not the time) of two VB.NET Date objects. Is there a way to do that?

Read More