存储过程报错
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
[color=#FF0000]这是最开始的存储过程，是对的。下面一段是我改过的就报错了。[/color]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 开始分钟 结束分钟都为''空
以前这个存储过程只写了日期和时间 后来我加了分钟 就报错了
[解决办法]
数据类型的问题
[解决办法]
smalldatetime可存储日期的范围是：
1900-01-01到2079-06-06
格式为：YYYY-MM-DD HH:MM:SS
你看看你的数据满足么？
[解决办法]
if ltrim(rtrim(@overtime_start))='24'
你查的是小时的值吗?不能为24,其值为0-23
