在 SQL Server Reporting Services (VB.Net) 中查找上一个日历月的第一天和最后一天

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

提问by Randall

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.

我正在 MS SQL Server Reporting Services 中创建一个报告，需要将默认的开始和结束日期报告参数设置为上一个日历月的第一个和最后一个日期，需要帮助。

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!

我一直在使用 DateAdd，但没有成功创建表达式（在我理解的 VB.NET 中）。我真的很感激你能给我的任何帮助！

回答by Ray

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:

Randall，这里是我发现在 SSRS 中使用的 VB 表达式，以获取任何月份的第一天和最后一天，使用当前月份作为参考：

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)functionexplains that the function accepts values outside the expected range for the year, month, and dayparameters. This allows you to specify useful date-relative values. For instance, a value of 0 for Daymeans "the last day of the preceding month". It makes sense: that's the day before day 1 of the current month.

所述的的VisualBasic MSDN文档DateSerial(year,month,day)功能解释该函数接受的预期范围以外的值的year，month和day参数。这允许您指定有用的日期相关值。例如，值为 0Day表示“上个月的最后一天”。这是有道理的：那是当月的第 1 天的前一天。

回答by Stephanie Grice

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)

回答by David M

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)

回答by SLaks

I'm not familiar with SSRS, but you can get the beginning and end of the previous month in VB.Net using the DateTimeconstructor, like this:

我不熟悉 SSRS，但是您可以使用DateTime构造函数在 VB.Net 中获取上个月的开始和结束，如下所示：

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

(yourDatecan be any DateTimeobject, such as DateTime.Todayor #12/23/2003#)

（yourDate可以是任何DateTime对象，例如DateTime.Today或#12/23/2003#）

回答by Randall

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.

我在将实际的 VB.NET 转换为 SSRS 使用的 Expression 子集时遇到了一些困难。你肯定激励了我，这就是我想出的。

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.

我知道 StartDate （上个月的第一天）有点递归。有什么我在这里想念的吗？这些是严格的日期字段（即没有时间），但我认为这应该捕获闰年等。

How did I do?

我是怎么做的？

回答by Jimmy

in C#:

在 C# 中：

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

回答by Riki Waikato

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

可以在以下位置找到更多详细信息：http: //msdn.microsoft.com/en-us/library/aa227522%28v=vs.60%29.aspx

回答by donviti

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

回答by dbasnett

        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