select @date2=left(@date2,11)+' '+@timea+':00' select @date2=@date2+1 -- drop table #single_emplio select eff_date into #single_emplio from iorec where empl_no=@empl_no and not eff_date is null and (eff_date between @date1 and @date2)
if @@rowcount >0 begin -- select @count=0 --while(@count <=@round) -- begin select @sin='aaaa',@bout='aaaa',@bin='aaaa',@sout='aaaa',@oin='aaaa',@oout='aaaa' select @sin_time=0,@bout_time=0,@bin_time=0,@sout_time=0,@oin_time=0,@oout_time=0 select @lamins_sum=0,@lamins='0000',@elmins_sum=0,@elmins='0000', @wkghrs_sum=0,@wkghrs='0000',@aftotmins_sum=0,@aftotmins='0000'
--上午進出時間 ......... ......... --下午進出時間 ......... ......... --遲到﹑早退 --加班時間 ......... ......... delete from attend where empl_no=@empl_no and year(date)=year(@date3) and month(date)=month(@date3) and day(date)=day(@date3) insert attend(date,empl_no,attlevel_n,duty,bni,sin,bout,bin,sout,oin,oout,ani,wkghrs,aftotmins,lamins,elmins,approved) values(@date9,@empl_no,@attlevel_n,'F',0,@sin,@bout,@bin,@sout,@oin,@oout,0,@wkghrs,@aftotmins,@lamins,@elmins,0) ....... .......
存儲過程如下:
CREATE procedure one_employee_attend
@empl_no varchar(15),
@date1 smalldatetime,
@date2 smalldatetime,
@attlevel_n varchar(5),
@timea varchar(5),
@timeb varchar(5),
@timec varchar(5),
@timed varchar(5),
@timee varchar(5),
@timef varchar(5),
@timeg varchar(5),
@timeh varchar(5),
@timei varchar(5)
as
declare @date3 smalldatetime
declare @date4 smalldatetime
declare @date5 smalldatetime
declare @date6 smalldatetime
declare @date7 smalldatetime
declare @date8 smalldatetime
declare @date9 smalldatetime declare @round int
declare @count int
declare @sin varchar(4)
declare @bout varchar(4)
declare @bin varchar(4)
declare @sout varchar(4)
declare @oin varchar(4)
declare @oout varchar(4)
declare @sin_time smalldatetime
declare @bout_time smalldatetime
declare @bin_time smalldatetime
declare @sout_time smalldatetime
declare @oin_time smalldatetime
declare @oout_time smalldatetime declare @lamins_sum int
declare @lamins varchar(4)
declare @elmins_sum int
declare @elmins varchar(4)
declare @wkghrs_sum int
declare @wkghrs varchar(4)
declare @aftotmins varchar(4)
declare @aftotmins_sum int
declare @gotime smalldatetime
declare @downtime smalldatetime /* select @empl_no='980945'
select @date1='2001/02/01'
select @date2='2001/02/28'
select @timea='06:00'
select @timeb='07:30'
select @timec='11:30'
select @timed='13:00'
select @timee='17:00'
select @check=1 */
select @round=datediff(day,@date1,@date2)
select @date9=@date1
select @date3=left(@date1,11)+' '+@timea+':00'
select @date4=left(@date1,11)+' '+@timec+':00'
select @date5=left(@date1,11)+' '+@timee+':00'
select @date6=@date3+1
select @date7=left(@date1,11)+' '+@timeb+':00'
select @date8=left(@date1,11)+' '+@timed+':00'
select @date2=left(@date2,11)+' '+@timea+':00'
select @date2=@date2+1 -- drop table #single_emplio
select eff_date into #single_emplio from iorec
where empl_no=@empl_no and not eff_date is null and (eff_date between @date1 and @date2)
if @@rowcount >0
begin
-- select @count=0
--while(@count <=@round)
-- begin
select @sin='aaaa',@bout='aaaa',@bin='aaaa',@sout='aaaa',@oin='aaaa',@oout='aaaa'
select @sin_time=0,@bout_time=0,@bin_time=0,@sout_time=0,@oin_time=0,@oout_time=0
select @lamins_sum=0,@lamins='0000',@elmins_sum=0,@elmins='0000',
@wkghrs_sum=0,@wkghrs='0000',@aftotmins_sum=0,@aftotmins='0000'
--上午進出時間
.........
.........
--下午進出時間
.........
.........
--遲到﹑早退
--加班時間
.........
.........
delete from attend where empl_no=@empl_no and year(date)=year(@date3) and month(date)=month(@date3) and day(date)=day(@date3)
insert attend(date,empl_no,attlevel_n,duty,bni,sin,bout,bin,sout,oin,oout,ani,wkghrs,aftotmins,lamins,elmins,approved)
values(@date9,@empl_no,@attlevel_n,'F',0,@sin,@bout,@bin,@sout,@oin,@oout,0,@wkghrs,@aftotmins,@lamins,@elmins,0)
.......
.......
类似这样的代码都有错。
@date3的类型是smalldatetime,而等号右边的操作会将结果转换成varchar型(因为@timea是varchar型)。等号两边数据类型不一样,赋值时自然就报错了。