SSRS表达式日期计算异常：添加18个月结果不符合预期

问题描述 #

我在报表中使用了以下表达式：

=iif(isdate(Fields!Date_of_Funding_Approval.Value),iif(isdate(Fields!Extension_Expiration_Date.Value),Fields!Extension_Expiration_Date.Value,DateAdd("m",18,Fields!Date_of_Funding_Approval.Value)),"")

其中Date_of_Funding_Approval为有效日期04/24/2019，Extension_Expiration_Date为NULL。预期结果是给Date_of_Funding_Approval加上18个月得到10/24/2020，但实际返回04/24/2025。

更新内容（表结构与测试数据） #

对应的SQL Server表结构及测试数据如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Master Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [New Project ID#] [nvarchar](255) NULL,
    [District Name] [nvarchar](255) NULL,
    [Program] [nvarchar](50) NULL,
    [School Name] [nvarchar](255) NULL,
    [Project Description] [nvarchar](255) NULL,
    [Qualifying Project Costs] [money] NULL,
    [Wealth Index] [float] NULL,
    [State Financial Participation] [money] NULL,
    [Notes] [nvarchar](max) NULL,
    [Means Value] [float] NULL,
    [Division Reviewed Project Size/POR] [int] NULL,
    [Primary Category] [nvarchar](50) NULL,
    [Secondary Category] [nvarchar](50) NULL,
    [Means Cost Center] [nvarchar](150) NULL,
    [Rescinded] [nvarchar](150) NULL,
    [Final] [nvarchar](150) NULL,
    [Green Incentive SFP] [money] NULL,
    [Date Printed] [datetime] NULL,
    [Manager Area] [nvarchar](255) NULL,
    [Grand Total SFP] [money] NULL,
    [Last_Updated] [datetime] NULL,
    [Extension Expiration Date] [datetime] NULL,
    [Extension Granted] [bit] NOT NULL,
    [Date of Funding Approval] [datetime] NULL,
    [Project Year] [nvarchar](255) NULL,
    [Final Cost SFP of Project] [money] NULL,
    [Project Shown in CMMS] [bit] NOT NULL,
    [Final Pay Inspection Name] [nvarchar](255) NULL,
    [Final Pay Inspection Date] [datetime] NULL,
    [Date Entered] [datetime] NULL,
    [Date Inspected] [datetime] NULL,
    [Percent Complete] [int] NULL,
    [Manager Name] [nvarchar](255) NULL,
    [Date of Report] [datetime] NULL,
    [Additional Notes] [ntext] NULL,
    [LEA] [int] NULL,
    [Stat Financial Participation Notes] [ntext] NULL,
    [username] [nvarchar](50) NULL,
    [conflict_timestamp] [timestamp] NULL,
 CONSTRAINT [PK_Master_Table] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Master Table] ON 
GO
INSERT [dbo].[Master Table] ([ID], [New Project ID#], [District Name], [Program], [School Name], [Project Description], [Qualifying Project Costs], [Wealth Index], [State Financial Participation], [Notes], [Means Value], [Division Reviewed Project Size/POR], [Primary Category], [Secondary Category], [Means Cost Center], [Rescinded], [Final], [Green Incentive SFP], [Date Printed], [Manager Area], [Grand Total SFP], [Last_Updated], [Extension Expiration Date], [Extension Granted], [Date of Funding Approval], [Project Year], [Final Cost SFP of Project], [Project Shown in CMMS], [Final Pay Inspection Name], [Final Pay Inspection Date], [Date Entered], [Date Inspected], [Percent Complete], [Manager Name], [Date of Report], [Additional Notes], [LEA], [Stat Financial Participation Notes], [username]) VALUES (3350, N'xxx-7207-010', N'xxx School District', N'xxx Partnership', N'-Not Specified-', N'xxx - Additions and Conversions', 7022599.0800, 0.43051886092115882, 3999237.7234, NULL, 153.58, 45726, N'Space', N'Conversion + Addition', N'xxx', NULL, NULL, NULL, CAST(N'2022-02-16T07:36:47.000' AS DateTime), N'1', 3999237.7234, CAST(N'2022-02-16T07:36:47.000' AS DateTime), NULL, 0, CAST(N'2019-04-24T00:00:00.000' AS DateTime), N'Year 1', NULL, 0, NULL, NULL, CAST(N'2019-04-29T00:00:00.000' AS DateTime), NULL, NULL, NULL, NULL, NULL, 7207, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[Master Table] OFF
GO
ALTER TABLE [dbo].[Master Table] ADD  CONSTRAINT [DF__Master Ta__Means__38845C1C]  DEFAULT ((0)) FOR [Means Value]
GO
ALTER TABLE [dbo].[Master Table] ADD  CONSTRAINT [DF__Master Ta__Divis__39788055]  DEFAULT ((0)) FOR [Division Reviewed Project Size/POR]
GO
ALTER TABLE [dbo].[Master Table] ADD  CONSTRAINT [DF__Master Ta__Green__3A6CA48E]  DEFAULT ((0)) FOR [Green Incentive SFP]
GO
ALTER TABLE [dbo].[Master Table] ADD  CONSTRAINT [DF__Master Ta__Exten__3B60C8C7]  DEFAULT ((0)) FOR [Extension Granted]
GO
ALTER TABLE [dbo].[Master Table] ADD  CONSTRAINT [DF__Master Ta__Proje__3C54ED00]  DEFAULT ((0)) FOR [Project Shown in CMMS]
GO

问题分析与修复方案 #

异常原因 #

SSRS的IIF函数会强制计算所有分支的表达式，无论该分支是否会被最终返回。当Extension_Expiration_Date为NULL时，虽然逻辑上应该执行日期加18个月的分支，但IIF仍会先尝试处理NULL值的分支，NULL在隐式转换中可能被解析为0，导致日期计算出现异常累加（比如误将18个月当成18年，或重复计算）。

修复方案 #

方案1：使用Switch函数（推荐）

Switch函数只会计算到第一个为True的条件分支，不会执行后续表达式，避免无效计算：

=Switch(
    Not IsNothing(Fields!Extension_Expiration_Date.Value) And IsDate(Fields!Extension_Expiration_Date.Value), Fields!Extension_Expiration_Date.Value,
    Not IsNothing(Fields!Date_of_Funding_Approval.Value) And IsDate(Fields!Date_of_Funding_Approval.Value), DateAdd("m",18,Fields!Date_of_Funding_Approval.Value),
    True, ""
)

方案2：提前判断NULL值

在IsDate判断前先检查字段是否为NULL，避免无效的隐式转换：

=IIf(Not IsNothing(Fields!Date_of_Funding_Approval.Value) And IsDate(Fields!Date_of_Funding_Approval.Value),
    IIf(Not IsNothing(Fields!Extension_Expiration_Date.Value) And IsDate(Fields!Extension_Expiration_Date.Value),
        Fields!Extension_Expiration_Date.Value,
        DateAdd("m",18,Fields!Date_of_Funding_Approval.Value)
    ),
    ""
)

方案3：在SQL数据源中处理逻辑（更可靠）

直接在查询中计算目标日期，彻底避免报表层表达式的潜在问题：

SELECT 
    [ID],
    [Date of Funding Approval],
    [Extension Expiration Date],
    CASE
        WHEN [Extension Expiration Date] IS NOT NULL THEN [Extension Expiration Date]
        WHEN [Date of Funding Approval] IS NOT NULL THEN DATEADD(MONTH, 18, [Date of Funding Approval])
        ELSE NULL
    END AS Calculated_Expiration_Date
FROM [dbo].[Master Table]

报表中直接引用Fields!Calculated_Expiration_Date.Value即可。

内容的提问来源于stack exchange，提问作者Randy