当前位置：文江博客话题详情

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

发布于 2024-08-17 09:06:13 字数 267 浏览 3 评论 0原文

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

该报告是在该月的第二个日历日生成的，我需要以下值：

上一个日历月
- 第一天
- 最后一天

我一直在使用 DateAdd，但没有成功创建表达式（据我所知，在 VB.NET 中）。我非常感谢您能给我的任何帮助！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼角的笑意。 2024-08-24 09:06:13

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

上个月的第一天：

=dateadd("m",-1,dateserial(year(Today),month(Today),1))

本月的第一天：

=dateadd("m",0,dateserial(year(Today),month(Today),1))

下个月的第一天：

=dateadd("m",1,dateserial(year(Today),month(Today),1))

上个月的最后一天：

=dateadd("m",0,dateserial(year(Today),month(Today),0))

本月的最后一天：

=dateadd("m",1,dateserial(year(Today),month(Today),0))

下个月的最后一天：

=dateadd("m",2,dateserial(year(Today),month(Today),0))

VisualBasic DateSerial(year,month,day) 函数的 MSDN 文档解释了该函数接受超出 year 预期范围的值，< code>month 和 day 参数。这允许您指定有用的相对日期值。例如，Day 的值 0 表示“上个月的最后一天”。这是有道理的：那是当月第一天的前一天。

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.

回复收藏 0 原文

羁拥 2024-08-24 09:06:13

这些功能对我非常有帮助——尤其是在设置订阅报告方面；但是，我注意到，当使用上面发布的当前月份的最后一天函数时，只要上个月与当前月份的天数相同，它就可以工作。我已经完成并测试了这些修改，希望它们将来能帮助其他开发人员：

日期公式：
查找上个月的第一天：

DateAdd("m", -1, DateSerial(Year(Today()), Month(Today()), 1))

查找上个月的最后一天：

DateSerial(Year(Today()), Month(Today()), 0)

查找当月的第一天：

DateSerial(Year(Today()),Month(Today()),1)

查找当月的最后一天：

DateSerial(Year(Today()),Month(DateAdd("m", 1, Today())),0)

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)

回复收藏 0 原文

自演自醉 2024-08-24 09:06:13

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)

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)

回复收藏 0 原文

岁月蹉跎了容颜 2024-08-24 09:06:13

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

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

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

回复收藏 0 原文

我恋#小黄人 2024-08-24 09:06:13

在 C# 中：

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

in C#:

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

回复收藏 0 原文

久光 2024-08-24 09:06:13

我在将实际 VB.NET 转换为 SSRS 使用的表达式子集时遇到了一些困难。不过，你确实启发了我，这就是我的想法。

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

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

我是怎么做的？

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?

回复收藏 0 原文

遗心遗梦遗幸福 2024-08-24 09:06:13

我正在寻找一个简单的答案来自己解决这个问题。这是我发现的

这将分割年份和月份，休息一个月并得到第一天。

firstDayInPreviousMonth = DateSerial(Year(dtmDate), Month(dtmDate) - 1, 1)

从当前获取上个月的第一天

lastDayInPreviousMonth = DateSerial(Year(dtmDate), Month(dtmDate), 0)

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

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

回复收藏 0 原文

九局 2024-08-24 09:06:13

这一个会给你日期而不是时间：

=FormatDateTime(DateAdd("m", -1, DateSerial(Year(Today()), Month(Today()), 1)), 
DateFormat.ShortDate)

这个会给你日期时间：

=dateadd("m",-1,dateserial(year(Today),month(Today),1))

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

回复收藏 0 原文

不打扰别人 2024-08-24 09:06:13

        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

        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

回复收藏 0 原文

~没有更多了~