CODEWITHSUNDEEP
sql-serverdecimalvarchar
Zhang18
Zhang18

Reputation: 4930

Convert long decimal or float to varchar in SQL Server

One of the table I'm trying to query has a field with type decimal(38,19). I need to convert it to varchar in order for my perl DBI module to handle. How should I write the conversion in SQL to make it work? Specifically, if I run this in SQL Server Management Studio:

select convert(varchar, 19040220000.0000000000000000000)

I get:

Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.

I tried to round the number first:

select convert(varchar, round(19040220000.0000000000000000000, 0))

but that doesn't seem to work either (same error message). In fact round() doesn't seem to have an effect on that number for some reason. What should I do? Thx.

Upvotes: 0

Views: 15165

Answers (2)

marc_s
marc_s

Reputation: 754368

If you don't specify a length for your varchar, it defaults to 30 characters in the case of a CONVERT operation.

That's not long enough to hold your 38-digit decimal. So give your varchar an appropriate length in the CONVERT statement!!

Try this:

select convert(varchar(40), 19040220000.0000000000000000000)

Upvotes: 4

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

You need to use a varchar with a set length larger than the precision you want, i.e.

select convert(varchar(64), 19040220000.0000000000000000000)

Upvotes: 0

Related Questions