Error converting float to varchar in SQL

Question

I've found an error while trying to convert a float to varchar, using "style 3" (view style doc in here: cast-and-convert-transact-sql )

CODE

select convert(varchar(max),convert(float,-1),3)  

ERROR:

Msg 232, Level 16, State 2, Line 81  
Arithmetic overflow error for type varchar, value = -0.500000.  

Has anyone else seen this error? I found a solution so I post it here .

CAUSE:
The issue comes from using varchar(max).

SOLUTION:
Instead of using varchar(max) use varchar(n) and the error goes away.

WORKARROUND:
You could also use the style = 2, but you wouldn't get the maximum precision in the convertion (according to the docs).

select convert(varchar(max),convert(float,-1),2)  

Answer 1

Interesting, looks like a bug to me.

Answer 2

Works for me/my SQL Server. Which version are you using? =>

SELECT @@VERSION  

Answer 3

Hi，@Juan Pablo Echevarria

Welcome to Microsoft T-SQL Q&A Forum!

Let me guess if your version is based on 2019linux, it seems that I have encountered this problem before, my approach is to receive the converted value through a variable, you can try it.

DECLARE @dt DECIMAL(38);  
SET @dt = convert(float,-1)  
SELECT CAST(@dt AS VARCHAR(max))  

]1

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.