reporting-services - SQL Server Reporting Services 表达式 BackgroundColour 基于 DateDiff

Question

如果值与今天的日期相同，我正在尝试使用 SQL Server Reporting Services 中的条件格式来更改行的颜色。该列包含格式为 13/07/2018 的日期。我还在占位符属性中将该字段设置为日期格式 (31/01/2000)。

但是我的表达不起作用

=switch(DateDiff("d", Fields!LastSuccessfulBackupTime00.Value,Format(Now(),"dd/MM/yyyy")) = 0, "Green",DateDiff("d", Fields!LastSuccessfulBackupTime00.Value,Format(Now(), "dd/MM/yyyy")) = 1, "Yellow",DateDiff("d", Fields!LastSuccessfulBackupTime00.Value,Format(Now(), "dd/MM/yyyy")) >= 2, "Red")

被抛出的异常是

参数匹配参数“DayOfWeek”从“String”缩小到“Mcrosoft.VisualBasic.FirstDayOfWeek”

这很奇怪，因为我只是在做简单的 datediff 计算来计算两个日期之间的天数。

谁能建议如何解决这个问题？谷歌只是说要关闭我在 SQLRS 中找不到的严格编译

标签： reporting-services

Answer 1

试试下面的表达式（表达式 1）

=switch(
DateDiff("d", Cdate(Fields!LastSuccessfulBackupTime00.Value),Today()) = 0, "Green"
,DateDiff("d", Cdate(Fields!LastSuccessfulBackupTime00.Value),Today()) = 1, "Yellow"
,DateDiff("d", Cdate(Fields!LastSuccessfulBackupTime00.Value), Today()) >= 2, "Red"
)

此外，由于 Cdate 依赖于区域设置，您可以使用 Split 为 datediff (expression2) 创建有效的日期格式

=switch(
DateDiff("d",  Fields!LastSuccessfulBackupTime00.Value.Split("/")(2) & "-" & Fields!LastSuccessfulBackupTime00.Value.Split("/")(1) & "-" & Fields!LastSuccessfulBackupTime00.Value.Split("/")(0),Today()) = 0, "Green"
,DateDiff("d",  Fields!LastSuccessfulBackupTime00.Value.Split("/")(2) & "-" & Fields!LastSuccessfulBackupTime00.Value.Split("/")(1) & "-" & Fields!LastSuccessfulBackupTime00.Value.Split("/")(0),Today()) = 1, "Yellow"
,DateDiff("d",  Fields!LastSuccessfulBackupTime00.Value.Split("/")(2) & "-" & Fields!LastSuccessfulBackupTime00.Value.Split("/")(1) & "-" & Fields!LastSuccessfulBackupTime00.Value.Split("/")(0),Today()) >= 2, "Red"
)