declare @tb table (num nvarchar(500)) insert into @tb select '114.57000000000001'select left(num,charindex('.',num)-1) from @tb114
select convert(int,114.57000000000001)
declare @tb table (num nvarchar(500)) insert into @tb select '114.57000000000001'select case when ceiling(num)>num then ceiling(num)-1 else num end as num from @tb114
declare @tb table (num nvarchar(500)) insert into @tb select '114.57000000000001' insert into @tb select 'a' --查询 select case when ceiling(num)>num then ceiling(num)-1 else num end as num from @tb where isnumeric(num)=1 --看看有什么数据不是数字类型的 select * from @tb where isnumeric(num)=0
将 nvarchar 值 '114.57000000000001' 转换为数据类型为 int 的列时发生语法错误。
insert into @tb select '114.57000000000001'select left(num,charindex('.',num)-1) from @tb114
select convert(int,114.57000000000001)
declare @tb table (num nvarchar(500))
insert into @tb select '114.57000000000001'select
case when ceiling(num)>num then ceiling(num)-1 else num end as num
from @tb114
试一下SELECT ROUND(150.75, 0, 1)就知道了
SET @myval = 114.57000000000001
SELECT CAST(CAST(@myval AS decimal(10,1)) as int)
将数据类型 nvarchar 转换为 numeric 时出错。我的数据在表格里面是整数导入里面就变成带小数的
select cast(floor('114.57000000000001') as int)
insert into @tb select '114.57000000000001'
insert into @tb select 'a'
--查询
select
case when ceiling(num)>num then ceiling(num)-1 else num end as num
from @tb
where isnumeric(num)=1
--看看有什么数据不是数字类型的
select * from @tb
where isnumeric(num)=0