关于日期类型的转换问题

[myhunan]

我有一存储过程

alter proc up_GetEmpCheckTime
(@v_deptlist varchar(2000), --部门列表
@dt_start datetime, --查询起始时间 ,指工作日
@dt_end datetime) --查询结束时间 ,指工作日
as
declare @errinfo varchar(2000)
begin
create table #t_emptime
-- 此临时表用于存放应打卡信息
( n_empid numeric(14,0) not null, v_empno varchar(10) not null,
v_chname varchar(20) not null, n_deptid integer null,
n_tsid integer not null, ch_aliase char(2) null,
dt_workdate datetime not null, dt_start1 datetime null,
i_amin1 int null, dt_end1 datetime null,
i_flag1 int null, i_smin1 int null,
dt_start2 datetime null, i_amin2 int null,
dt_end2 datetime null, i_smin2 int null,
i_flag2 int null, dt_start3 int null,
i_amin3 int null, dt_end3 datetime null,
i_smin3 int null, i_flag3 int null,
dt_start4 datetime null, i_amin4 int null,
dt_end4 datetime null, i_smin4 int null,
i_flag4 int null, i_workdaymins int null
)
insert into #t_emptime
select n_empid, v_empno, v_chname, n_deptid, n_tsid,
null, dt_workdate, null,null,null,null,null,
null, null, null, null, null, null,null, null,
null, null, null, null, null, null,null, null
from rs_check_tsinemp
where (convert( varchar(10), n_deptid) in( @v_deptlist ) )
and dt_workdate between @dt_start and @dt_end
if @@error <> 0 print convert(varchar(10) , @@error)
if @@error <> 0 goto errorinfo
update #t_emptime set
#t_emptime.dt_start1 = ts.dt_start1,
#t_emptime.i_amin1 = ts.i_amin1,
#t_emptime.dt_end1 = case when datediff(minute, ts.dt_start1, ts.dt_end1) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end1, 120), 12, 5 ))
when datediff(minute, ts.dt_start1, ts.dt_end1) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 11) +' ' + substring(convert(varchar(20), ts.dt_end1, 120), 12, 5 )) + 1
else '1900-01-01' end,
#t_emptime.i_flag1 = ts.i_flag1,
#t_emptime.i_smin1 = ts.i_smin1,
#t_emptime.dt_start2 = case when datediff(minute, ts.dt_start1, ts.dt_start2) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_start2, 120), 12, 5 ))
when datediff(minute, ts.dt_start1, ts.dt_start2) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' '+ substring(convert(varchar(20), ts.dt_start2, 120), 12, 5 )) + 1
else '1900-01-01' end,
#t_emptime.i_amin2 = ts.i_amin2,
#t_emptime.dt_end2 = case when datediff(minute, ts.dt_start1, ts.dt_end2) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end2, 120), 12, 5 ))
when datediff(minute, ts.dt_start1, ts.dt_end2) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end2, 120), 12, 5 )) + 1
else '1900-01-01' end,
#t_emptime.i_flag2 = ts.i_flag2,
#t_emptime.i_smin2 = ts.i_smin2,
#t_emptime.dt_start3 = case when datediff(minute, ts.dt_start1, ts.dt_start3) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_start3, 120), 12, 5 ))
when datediff(minute, ts.dt_start1, ts.dt_start3) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end2, 120), 12, 5 )) + 1
else '1900-01-01' end,
#t_emptime.i_amin3 = ts.i_amin3,
#t_emptime.dt_end3 = case when datediff(minute, ts.dt_start1, ts.dt_end3) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end3, 120), 12, 5 ))
when datediff(minute, ts.dt_start1, ts.dt_end3) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end3, 120), 12, 5 )) + 1
else '1900-01-01' end,
#t_emptime.i_flag3 = ts.i_flag3,
#t_emptime.i_smin3 = ts.i_smin3,
-- #t_emptime.dt_start4= case when datediff(minute, ts.dt_start1, ts.dt_start4) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_start4, 120), 12, 5 ))
-- when datediff(minute, ts.dt_start1, ts.dt_start4) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_start4, 120), 12, 5 )) + 1
-- else '1900-01-01' end,
#t_emptime.i_amin4 = ts.i_amin4,
-- #t_emptime.dt_end4 = case when datediff(minute, ts.dt_start1, ts.dt_end4) > 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end4, 120), 12, 5 ))
-- when datediff(minute, ts.dt_start1, ts.dt_end4) < 0 then convert(datetime,substring(convert(varchar(20), #t_emptime.dt_workdate, 120), 1, 10) +' ' + substring(convert(varchar(20), ts.dt_end4, 120), 12, 5 )) + 1
-- else '1900-01-01' end,
-- #t_emptime.i_flag4 = ts.i_flag4,
-- #t_emptime.i_smin4 = ts.i_smin4,
#t_emptime.i_workdaymins = ts.i_workdaymins, #t_emptime.ch_aliase = ts.ch_aliase
from #t_emptime , rs_check_timesect ts
where #t_emptime.n_tsid = ts.n_tsid
if @@error <> 0 print convert(varchar(10) , @@error)
if @@error<> 0 goto errorinfo
select * from #t_emptime
drop table #t_emptime
return
end
errorinfo:
select @errinfo = '生成应打卡时间表失败:' + convert(varchar(10) ,@@error)
drop table #t_emptime
RAISERROR( @errinfo, 16, 1)


--execute up_GetEmpCheckTime '@#$','2006-01-01','2007-01-01'

老执行时老报错, 错误信息
" 服务器: 消息 260，级别 16，状态 1，过程 up_GetEmpCheckTime，行 35
不允许从数据类型 datetime 到数据类型 int 的隐性转换（表 'tempdb.dbo.#t_emptime__________________________________________________________________________________________________________00010000000F'，列 'dt_start3'）。请使用 CONVERT 函数来运行此查询。 "

我调试时,发现在是case 语句时错误,但我不解的是 此update语句中前四个 case 是正常,后面的就报错,但语法结构是一样

[OracleRoob]

把其它不出错的case when注释掉，只保留出错的case when试试。

是否ts.dt_end4的值有问题

[myhunan]

我调试时,就是将其它的case when注释了才调试出来的, dt_end4的值绝对没问题,是日期时间型的,而且有值

[OracleRoob]

dt_start3 int null,

这里错了，应该定义为datetime型

[myhunan]

呵~~,Thank you!, 这问题我找了好久,就没想是定义错误