你好，请问该如何修改to_char() 在sqlserver2008下能运行？

Question

Select b.unit_name ,a.amount1,a.amount2,a.amount3,a.amount ,
to_char((a.Rate1 * 100),'fm9999999990.00') || '%' Rate1,to_char((a.Rate2 * 100),'fm9999999990.00') || '%' Rate2,to_char((a.Rate3 * 100)),
to_char((a.Rate * 100),'fm9999999990.00') || '%' Rate
From rv_tmp_report_sideways a,rv_unit b Where a.unit_id = b.unit_id
如何修改to_char() 在sqlserver2008下能运行

Answer 1

用Round() + Cast()就可以：Select b.unit_name ,a.amount1,a.amount2,a.amount3,a.amount ,cast(Round((a.Rate1 * 100),2) as varchar(13))+'%' Rate1,cast(Round((a.Rate2 * 100),2) as varchar(13))+'%' Rate2,cast(Round((a.Rate3 * 100),2) as varchar(13)),cast((Round((a.Rate * 100),2) as varchar(13)) +'%' RateFrom rv_tmp_report_sideways a,rv_unit b Where a.unit_id = b.unit_id

Answer 2

若能保证a.Rate可以强行转换为数字(就是说里面保存的只有数字)的话可以使用 Convert(decimal(18,2),a.Rate1 * 100) 来转换成格式为保留小数点后2位数字,总长度最高18位的数字.若是a.Rate还含有小数的话 修改为Convert(decimal(18,2),a.Rate1 * 100.00)即可执行