使用计算列触发器时出现日期/时间字符串转换失败错误求助

咱们一步步来解决你遇到的问题，主要分为日期转换错误和触发器逻辑缺陷两个部分：

1. 解决日期转换失败的问题 #

你遇到的Msg 241错误，核心原因是SQL Server无法识别你使用的dd-mm-yyyy格式日期字符串。SQL Server的日期解析规则依赖于服务器的语言/区域设置（比如美式环境默认是mm-dd-yyyy），当你插入16-05-2018时，它会尝试把16当作月份，这显然不符合月份范围，所以报错。

修复方法（二选一即可） #

方法一：使用ISO标准日期格式（推荐）

ISO格式yyyy-mm-dd是SQL Server全局兼容的日期格式，不受服务器设置影响，直接修改插入语句：

insert into book_issue_return_details(issued_date, return_date, payed)
values
('2018-05-16','2018-05-17',0),
('2018-05-07','2018-05-17',0),
('2018-05-01','2018-05-17',0),
('2018-05-17','2018-05-17',0),
('2018-04-01','2018-05-17',0);

方法二：用CONVERT函数指定格式

如果你坚持使用dd-mm-yyyy格式，可以用CONVERT函数明确指定格式代码（103对应dd/mm/yyyy）：

insert into book_issue_return_details(issued_date, return_date, payed)
values
(CONVERT(date, '16-05-2018', 103), CONVERT(date, '17-05-2018', 103), 0),
(CONVERT(date, '07-05-2018', 103), CONVERT(date, '17-05-2018', 103), 0),
(CONVERT(date, '01-05-2018', 103), CONVERT(date, '17-05-2018', 103), 0),
(CONVERT(date, '17-05-2018', 103), CONVERT(date, '17-05-2018', 103), 0),
(CONVERT(date, '01-04-2018', 103), CONVERT(date, '17-05-2018', 103), 0);

2. 修复触发器与计算列的逻辑缺陷 #

即使解决了日期问题，你的代码仍然会出现逻辑错误，需要调整：

问题一：for_days计算列的逻辑错误 #

你当前的计算列是：

for_days as datediff(day, return_date, issued_date) persisted

DATEDIFF(day, 结束日期, 开始日期)会得到负数（因为issued_date早于return_date），而触发器里的罚款判断都是和正数比较，完全不符合预期。应该把参数顺序反过来，计算实际借阅天数：

for_days as datediff(day, issued_date, return_date) persisted

问题二：触发器的逻辑错误 #

你的触发器存在三个严重问题：

1. 读取整个表的for_days赋值给变量，会导致所有行的penalty被更新为最后一行的计算结果；
2. 用字符串（比如'8'）和数字类型的for_days比较，存在隐式转换风险；
3. CASE语句没有覆盖所有天数范围（比如9-14天、16-29天会返回NULL）。

修复后的触发器代码：

create trigger trforinsertpenalty on book_issue_return_details
after insert
as
begin
    set nocount on; -- 避免返回多余的影响行数消息
    update bir
    set penalty = case
        when bir.for_days < 8 then 0
        when bir.for_days = 8 then 10
        when bir.for_days between 9 and 14 then 10 -- 补充覆盖中间天数，可根据需求调整
        when bir.for_days = 15 then 20
        when bir.for_days between 16 and 29 then 20 -- 补充覆盖中间天数
        when bir.for_days = 30 then 30
        when bir.for_days > 30 then 100
        else 0 -- 默认值，避免NULL
    end
    from book_issue_return_details bir
    inner join inserted i on bir.issue_id = i.issue_id; -- 只更新刚插入的行
end

最终验证 #

先删除原表和触发器，重新创建修正后的表和触发器，再执行修复后的插入语句，就能正常插入数据并自动计算罚款了。

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