CODEWITHSUNDEEP
sqlsql-servert-sql
Bob Wakefield
Bob Wakefield

Reputation: 4009

With TSQL, how do I convert a highly precise value to numeric?

I'm having a pretty common problem but my usual solution isn't working. I've got a highly precise value stored as a character in a staging table. When I push it to it's final destination, a column with data type numeric(38,38), it fails. I thought it was because of the negative sign, but when I get rid of it, I still have an issue. I've got the numeric column maxed out but I'm still getting the following error: Arithmetic overflow error converting nvarchar to data type numeric.

Normally I just convert to float but that isn't working. Besides which, I want to retain the precision of the value and float appears to take that away.

What am I missing here?

DECLARE @Value NVARCHAR(255) = '-1.000000000000000'
SELECT CAST(@Value AS numeric(38,38))

DECLARE @Value NVARCHAR(255) = '-1.000000000000000'
SELECT CAST(CAST(@Value AS FLOAT) AS numeric(38,38))

CLARIFICATION: numeric(38,38) are absurd parameters and were only used for testing and as an example for this question. The original column was set to numeric(16,15) which worked more than 99% of the time on a dataset of millions of records and thus didn't flag as an issue.

Upvotes: 0

Views: 129

Answers (1)

Andrew O'Brien
Andrew O'Brien

Reputation: 1813

numeric(38,38) is a numeric value of 38 digits, 38 of which are after the decimal. Because of this, any number greater than 1 or less than -1 (has 1 or more digits to the left of the decimal) will overflow. You need to account for the maximum number of digits that may occur to both the left and the right of the decimal.

1.00001 would be numeric(6,5) with 6 digits, 5 of which are on the right of the decimal, 1 of which is on the left.

10.00001 would be numeric(7,5) with 7 digits, 5 of which are on the right of the decimal, 2 of which are on the left.

Upvotes: 3

Related Questions