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

Question

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

User · Answer

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 · Answer

I was looking for a simple answer to solve this myself  here is what I found  This will split the year and month  take one month off and get the first day   firstDayInPreviousMonth   DateSerial Year dtmDate   Month dtmDate  - 1  1    Gets the first day of the previous month from the current  lastDayInPreviousMonth   DateSerial Year dtmDate   Month dtmDate   0    More details can be found at  http   msdn microsoft com en-us library aa227522 28v vs 60 29 aspx

User · Answer

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 · Answer

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

User · Answer

This one will give you date no time    FormatDateTime DateAdd  m   -1  DateSerial Year Today     Month Today     1     DateFormat ShortDate    This one will give you datetime    dateadd  m  -1 dateserial year Today  month Today  1

User · Answer

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 · Answer

in C    new DateTime DateTime Now Year  DateTime Now Month  1  AddMonths -1  new DateTime DateTime Now Year  DateTime Now Month  1  AddDays -1

User · Answer

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

User · Answer

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

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

Examples related to vb.net

Examples related to date

Examples related to reporting-services

Examples related to calendar