从数据类型 varchar 转换为 numeric 时出错。
update #tempEmployeesAttendance
set LateClass1 =
(case when convert(varchar(12),OnDuty1,14)='00:00:00:000' then (case when convert(varchar,ClassUP1,8)>convert(varchar,OnDuty1,8) AND convert(varchar,ClassUP1,8)<convert(varchar,OffDuty1,8) then convert(varchar,(ClassUP1-OnDuty1),8) else null end)
else (case when convert(varchar,ClassUP1,8) > convert(varchar,OnDuty1,8) then convert(varchar,(ClassUP1-OnDuty1),8) else null end)end )
这句为什么会提示转换为 numeric 时出错。
LateClass1字段试过datetime,nvarchar(50),decmail(4,2)都试过还是报这个错
update #tempEmployeesAttendance
set LateClass1 =
(case when convert(varchar(12),OnDuty1,14)='00:00:00:000' then (case when convert(varchar,ClassUP1,8)>convert(varchar,OnDuty1,8) AND convert(varchar,ClassUP1,8)<convert(varchar,OffDuty1,8) then convert(varchar,(ClassUP1-OnDuty1),8) else null end)
else (case when convert(varchar,ClassUP1,8) > convert(varchar,OnDuty1,8) then convert(varchar,(ClassUP1-OnDuty1),8) else null end)end )
这句为什么会提示转换为 numeric 时出错。
LateClass1字段试过datetime,nvarchar(50),decmail(4,2)都试过还是报这个错
ClassUP1-OnDuty1
这样的运算时,数据类型不对.
set LateClass1 =
ISNUMERIC( (case when convert(varchar(12),OnDuty1,14)='00:00:00:000' then (case when convert(varchar,ClassUP1,8)>convert(varchar,OnDuty1,8) AND convert(varchar,ClassUP1,8)<convert(varchar,OffDuty1,8) then convert(varchar,(ClassUP1-OnDuty1),8) else null end)
else (case when convert(varchar,ClassUP1,8) > convert(varchar,OnDuty1,8) then convert(varchar,(ClassUP1-OnDuty1),8) else null end)end ))
select (case when convert(varchar(12),OnDuty1,14)='00:00:00:000'
then (case when convert(varchar,ClassUP1,8)>convert(varchar,OnDuty1,8)
AND convert(varchar,ClassUP1,8)<convert(varchar,OffDuty1,8)
then convert(varchar,(ClassUP1-OnDuty1),8) else null end)
else (case when convert(varchar,ClassUP1,8) > convert(varchar,OnDuty1,8)
then convert(varchar,(ClassUP1-OnDuty1),8) else null end)end )
from #tempEmployeesAttendance
NULL
00:50:11
NULL
00:30:00
NULL
00:30:00
NULL
07:30:00(8 row(s) affected)
[SQL]
我把他改为datetime为啥也是不对呢?
declare @a datetime,@b datetime
set @a='00:30:00';set @b=null
select @a,@b
------------------------------ -------------
1900-01-01 00:30:00.000 NULL(所影响的行数为 1 行)