存储过程报错

[花样小星]

USE [gansov35]
GO
/****** Object: StoredProcedure [dbo].[I_Adaptor_OT_APPROVED] Script Date: 03/30/2012 13:59:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER proc [dbo].[I_Adaptor_OT_APPROVED]
(@sheet_id varchar(50), --单号
@initializedBy varchar(50)--确认人
)

as
begin
declare @employee varchar(50)
declare @code varchar(50)
declare @userids varchar(50)
declare @EID int


declare @Name nvarchar(40)
declare @Depid varchar(10)

declare @overtime_start nvarchar(50)
declare @overtime_end nvarchar(50)

set @employee=''
set @code=''
set @userids=''



select @userids=employee_number from T01_OverTime_detail where overtime_id=@sheet_id

select @employee=employeeId from sys_user where userid=@userids


SELECT @Name=sys_user.fullname, @Depid=sys_orgmenber.departmentid ,@EID=sys_user.eid
FROM sys_user INNER JOIN sys_orgmenber ON sys_user.username = sys_orgmenber.member where sys_user.EmployeeID=@employee


select @code=tyle from v01_type where overtime_id=@sheet_id

--判断加班开始和结束时间是否为24点，如果是，改为23点59分59秒

select @overtime_start=overtime_start,@overtime_end=overtime_end from T01_OverTime_detail where overTime_id=@sheet_id

if ltrim(rtrim(@overtime_start))='24'
set @overtime_start='23:59:59'
else
set @overtime_start=@overtime_start+':00:00'


if ltrim(rtrim(@overtime_end))='24'
set @overtime_end='23:59:59'
else
set @overtime_end=@overtime_end+':00:00'

if @employee is null or @employee='' or @EID is null or @EID =''
return



begin tran T1

insert into [dbo].[aRegTime_Register](Term,badge,SheetID,freqType,twid,unit,WfStatus,reason,Name,DepID,initialized,initializedBy,initializedTime,EID) values(CONVERT(varchar(20),getdate()),@employee,'OT','1',@code,'H','0',@sheet_id,@Name,@Depid,1,@initializedBy,GETDATE(),@EID)

update [dbo].[aRegTime_Register] set
OTTerm=overtime_starttime,
BeginTime=overtime_starttime+' '+@overtime_start,
EndTime=overtime_starttime+' '+@overtime_end,
Amount=overtime_time,
content=overtime_reason,
remark=overtime_remark
from T01_OverTime_detail A inner join [dbo].[aRegTime_Register] B on B.reason=@sheet_id where A.overTime_id=@sheet_id

commit tran T1

end
这是最开始的存储过程，是对的。下面一段是我改过的就报错了。USE [gansov35]GO
/****** Object: StoredProcedure [dbo].[I_Adaptor_OT_APPROVED] Script Date: 03/30/2012 11:14:16 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER proc [dbo].[I_Adaptor_OT_APPROVED]
(@sheet_id varchar(50), --单号
@initializedBy varchar(50)--确认人
)

as
begin
declare @employee varchar(50)
declare @code varchar(50)
declare @userids varchar(50)
declare @EID int


declare @Name nvarchar(40)
declare @Depid varchar(10)

declare @overtime_start nvarchar(50)
declare @overtime_end nvarchar(50)
declare @OverTime_sminute nvarchar(50)
declare @OverTime_eminute nvarchar(50)

set @employee=''
set @code=''
set @userids=''



select @userids=employee_number from T01_OverTime_detail where overtime_id=@sheet_id

select @employee=employeeId from sys_user where userid=@userids


SELECT @Name=sys_user.fullname, @Depid=sys_orgmenber.departmentid ,@EID=sys_user.eid
FROM sys_user INNER JOIN sys_orgmenber ON sys_user.username = sys_orgmenber.member where sys_user.EmployeeID=@employee


select @code=tyle from v01_type where overtime_id=@sheet_id

--判断加班开始和结束时间是否为24点，如果是，改为23点59分59秒
select @overtime_start=overtime_start,@overtime_end=overtime_end from T01_OverTime_detail where overTime_id=@sheet_id
select @OverTime_sminute=OverTime_sminute,@OverTime_eminute=OverTime_eminute from T01_OverTime_detail where overTime_id=@sheet_id
if ltrim(rtrim(@overtime_start))='24'
set @overtime_start='23:59:59'
else if ltrim(rtrim(@OverTime_sminute))='' or ltrim(rtrim(@OverTime_sminute))is null
set @OverTime_sminute='00'
else
set @overtime_start=@overtime_start+':'+@OverTime_sminute+':00'


if ltrim(rtrim(@overtime_end))='24'
set @overtime_end='23:59:59'
else if ltrim(rtrim(@OverTime_eminute))='' or ltrim(rtrim(@OverTime_eminute))is null
set @OverTime_eminute='00'
else
set @overtime_end=@overtime_end+':'+@OverTime_eminute+':00'

if @employee is null or @employee='' or @EID is null or @EID =''
return



begin tran T1

insert into [dbo].[aRegTime_Register](Term,badge,SheetID,freqType,twid,unit,WfStatus,reason,Name,DepID,initialized,initializedBy,initializedTime,EID) values(CONVERT(varchar(20),getdate()),@employee,'OT','1',@code,'H','0',@sheet_id,@Name,@Depid,1,@initializedBy,GETDATE(),@EID)

update [dbo].[aRegTime_Register] set
OTTerm=overtime_starttime,
BeginTime=overtime_starttime+' '+@overtime_start,
EndTime=overtime_starttime+' '+@overtime_end,
Amount=overtime_time,
content=overtime_reason,
remark=overtime_remark
from T01_OverTime_detail A inner join [dbo].[aRegTime_Register] B on B.reason=@sheet_id where A.overTime_id=@sheet_id

commit tran T1

end

就简单的改了几句话，
报错如下
从nvarchar数据类型到smalldatetime数据类型转换超出了一个范围的值
求高手指教
开始日期值和结束日期为2012-03-14 开始时间 9 结束时间 15 开始分钟 结束分钟都为''空
以前这个存储过程只写了日期和时间 后来我加了分钟 就报错了

[xuam]

数据类型的问题

[开着拖拉机泡妞]

smalldatetime可存储日期的范围是：
1900-01-01到2079-06-06
格式为：YYYY-MM-DD HH:MM:SS
你看看你的数据满足么？

[花样小星]

开始日期值和结束日期为2012-03-14 开始时间 9 结束时间 15 开始分钟 结束分钟都为''空
按照存储过程里的判断 应该是 2012-03-14 9:00:00 结束时间应该是2012-03-14 15:00:00
我刚刚去print查出来的参数 竟然爆print语法错误
[Quote=引用 2 楼 的回复:]
smalldatetime可存储日期的范围是：
1900-01-01到2079-06-06
格式为：YYYY-MM-DD HH:MM:SS
你看看你的数据满足么？
[/Quote]

[dawugui]

if ltrim(rtrim(@overtime_start))='24'

你查的是小时的值吗?不能为24,其值为0-23

[花样小星]

额，系统里面有个维护表，里面的值是1-24，判断还是0-23.这句话不用管。之前的存储过程也是这样写的，没报错。我关心的是我加的那几句有什么问题。
[Quote=引用 4 楼 的回复:]
if ltrim(rtrim(@overtime_start))='24'

你查的是小时的值吗?不能为24,其值为0-23
[/Quote]

[花样小星]

问题未解决，求解释呀。