reporting-services - DateDiff DateInterval.WeekOfYear 返回不一致的结果

Question

我在计算删除两个日期之间的周末天数时遇到问题。完整的公式是...

=DateDiff(DateInterval.day, Fields!FromTime.Value, Fields!ToTime.Value) + 1)
 - (DateDiff(DateInterval.WeekOfYear, Fields!FromTime.Value, Fields!ToTime.Value) * 2)
 - (iif(Weekday(Fields!FromTime.Value) = 7, 1, 0)
 - (iif(Weekday(Fields!ToTime.Value) = 6, 1, 0))
 - 1)

...但我已将我的具体问题隔离到 DateDiff 部分（DateDiff(DateInterval.WeekOfYear, Fields!FromTime.Value, Fields!ToTime.Value)），因为这并不总是正确计算差异。

附表（在 DateDiff=1 上过滤）显示了年份周“从”和“到”部分，然后最后一列返回 DateDiff 计算。比较 To/From 周，您可以看到一些超过一周的差异，尽管计算总是返回 1？

结果表

标签： reporting-servicesssrs-2008-r2reportbuilder

Answer 1

自己找到了问题的原因，出于某种原因，SSRS 不喜欢对日期和时间字符串进行 DateDiff 周比较（可能幅度太大？）。所以我的解决方案是仅针对日期部分找到 DateDiff，将其转换为秒，然后在秒上从原始 DateDiff 中删除结果。

=DateDiff(Dateinterval.Second, Fields!FromTime.Value, Fields!ToTimeNow.Value, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFullWeek)
- ((DateDiff(DateInterval.WeekOfYear, Fields!FromDate.Value, Fields!ToDate.Value, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFullWeek) * 2) * 86400)
- iif(Weekday(Fields!FromDate.Value, 1) = 1, 86400, 0)
- iif(Weekday(Fields!FromDate.Value, 1) = 7, 86400, 0)
- iif(Weekday(Fields!ToDate.Value, 1) = 1, 86400, 0)
- iif(Weekday(Fields!ToDate.Value, 1) = 7, 86400, 0)