时间戳

首页 > 解决方案 > 在计算列上使用触发器时从字符串转换日期和/或时间时转换失败

sql-server-2008 - 在计算列上使用触发器时从字符串转换日期和/或时间时转换失败

问题描述

桌子

create table book_issue_return_details(
issue_id int primary key identity(1,1), 
issued_date date, 
return_date date, 
for_days as datediff(day, return_date, issued_date) persisted, 
penalty int,
payed int, 
balance AS (penalty-payed) PERSISTED);

扳机

create trigger trforinsertpenalty
on book_issue_return_details
after insert
as
begin
    declare @value int
    select @value=for_days from book_issue_return_details;
    update book_issue_return_details 
        set penalty=case
        when for_days<'8' then 0
        when for_days='8' then 10
        when for_days='15' then 20
        when for_days='30' then 30 
        when for_days>'30' then 100
        end 
end

插入语句

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

错误

消息 241，级别 16，状态 1，第 1 行从字符串转换日期和/或时间时转换失败。

标签： sql-server-2008

解决方案

只需尝试以下插入语句..它对我有用。

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

推荐阅读