SQL server 问题

[「已注销」]

写了一个存储过程

create proc proc_kaoqin
@EmployeeID varchar(20)
as
declare @time1 datetime
declare @time2 datetime
declare @time3 datetime
set @time1=null
set @time2=null
set @time3=null

begin
set @time1=(select AmUpCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select AmUpTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3<0)
begin
insert into CheckOn(EmployeeID,TodayDate,AmUpWorkState,AmLateTime) values(@EmployeeID,CONVERT(varchar(10), getdate(), 120),'正常',0)
end
else if(@time3>0)
begin
insert into CheckOn(EmployeeID,TodayDate,AmUpWorkState,AmLateTime) values(@EmployeeID,CONVERT(varchar(10), getdate(), 120),'迟到',@time3)
end
end
else if(@time1 is null)
begin
insert into CheckOn(EmployeeID,TodayDate,AmUpWorkState,AmLateTime) values(@EmployeeID,CONVERT(varchar(10), getdate(), 120),'旷工',@time3)
end
end


begin
begin
set @time1=(select AmDownCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select AmDownTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3>0)
begin
update CheckOn set AmDownWorkState = '正常' , AmEarlyTime=0 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
else if(@time3<0)
begin
update CheckOn set AmDownWorkState = '迟到' , AmEarlyTime=@time3 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
else if(@time1 is null)
begin
update CheckOn set AmDownWorkState = '旷工' , AmEarlyTime=null where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
end
begin
begin
set @time1=(select PmUpCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select PmUpTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3>0)

begin
update CheckOn set PmUpWorkState = '正常' , PmLateTime=0 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
else if(@time3<0)
begin
update CheckOn set PmUpWorkState = '迟到' , PmLateTime=@time3 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end

else if(@time1 is null)
begin
update CheckOn set PmUpWorkState = '旷工' , PmLateTime=null where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
end
begin
begin
set @time1=(select PmDownCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
set @time2=(select PmDownTime from CompanyTimeRule )
set @time3=datediff(MI,@time1,@time2)
end
begin
if(@time1 !=null)
begin
if(@time3>0)
begin
update CheckOn set PmDownWorkState = '正常' , PmEarlyTime=0 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
else if(@time3<0)
begin
update CheckOn set PmDownWorkState = '迟到' , PmEarlyTime=@time3 where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
else if(@time1 is null)
begin
update CheckOn set PmDownWorkState = '旷工' , PmEarlyTime=NULL where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120)
end
end
end
创建成功
可是执行存储过程时却有错了。
exec proc_kaoqin '13'

报错：

消息 8115，级别 16，状态 2，过程 proc_kaoqin，第 14 行
将 expression 转换为数据类型 datetime 时出现算术溢出错误。

(0 行受影响)

(0 行受影响)

(0 行受影响)


这是什么错误，怎么解决。？？？

[shoppo0505]

第一行：
set @time1=(select AmUpCardTime from ChuQin where EmployeeID=@EmployeeID and TodayDate=CONVERT(varchar(10), getdate(), 120))
这个里面TodayDate是日期型么？那你后面的要写成：CONVERT(Date, getdate(), 120))

我看你后面还有很多次TodayDate 转成了Date的字符串。其实你直接把TodayDate定义成你需要的类型，这种转换就都可以避免了。

[汤姆克鲁斯]

set @time3=datediff(MI,@time1,@time2)

datediff返回的结果是int类型
你把它定义成datetime 这里可能会发生错误

[「已注销」]

TO 汤姆克鲁斯：

谢谢了！