SQL Server 2005 : Converting varchar value '1.23E-4' to decimal fails
declare @a varchar(40)
set @a='1.23e-4'
declare @b decimal(27,12)
if isnum开发者_如何转开发eric(@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.
Error converting data type varchar to numeric
anyone knows why?
thanks.
SELECT @b = CONVERT(REAL, @a, 2)
Scientific notation only works on FLOAT
and REAL
.
cast via float first.
SQL is quite strict about decimal
eg
SELECT CAST('' AS float), CAST('' AS int), CAST('' AS float) --0
SELECT CAST('' AS decimal) --error
精彩评论