Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 400
Randall Avatar asked Jan 13 '10 20:01

Randall


People also ask

How do I get previous month in SSRS?

First you DateAdd(DateInterval. Month, -1, ...) substract one month from the date you specified ( ... ), and then format the result date.

What is CDate in SSRS?

The CDate function converts the value to a date. The Now function returns a date value containing the current date and time according to your system. DateDiff returns a Long value specifying the number of time intervals between two Date values.


2 Answers

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.

like image 193
Ray Avatar answered Oct 04 '22 02:10

Ray


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) 
like image 45
Stephanie Grice Avatar answered Oct 04 '22 04:10

Stephanie Grice