Search code examples
sql-serverdecimalvarchar

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.

Solution

  • 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)