SQL Server 2005：将varchar值'1.23E-4'转换为十进制失败

declare @a varchar(40)
set @a='1.23e-4'
declare @b decimal(27,12)

if isnumeric(@a) =1
begin

    select @b=cast(@a as decimal(27,12))

end
else
begin
    select @b=-1
end

select @b

when exeucting above sql code under SQL 2005 environment I am getting following error.

当在SQL 2005环境下执行上面的sql代码时,我收到以下错误。

Error converting data type varchar to numeric

将数据类型varchar转换为数字时出错

anyone knows why?

有谁知道为什么?

thanks.

2 个解决方案

#1

SELECT  @b = CONVERT(REAL, @a, 2)

Scientific notation only works on FLOAT and REAL.

科学记数法仅适用于FLOAT和REAL。

#2

cast via float first.

首先通过浮动施放。

SQL is quite strict about decimal

SQL对十进制非常严格

SELECT CAST('' AS float), CAST('' AS int), CAST('' AS float) --0
SELECT CAST('' AS decimal) --error

#1

SELECT  @b = CONVERT(REAL, @a, 2)