Error converting nvarchar to decimal

[njahagirdar]

Hello Team,

Good Evening!!

I am trying to insert a record to SQL server which contains nvarchar and can someone help me in getting it converted to decimal because I receive the error mentioned below when I try to insert the record.

Error: Error converting data type nvarchar to decimal., error code: 8114, SQL state: S0005
Also the data looks something like this:

{“dataColumn”:-3.56443E-35}

Any help would be much appreciated.

Thanks in Advance,
Neeraj

[mbowen]

Hi Neeraj:

I suspect the problem is with the scientific notation. This is a very small negative number. Some experiments on the SQL server side.

SELECT ISNUMERIC('-3.56443E-35')
    -> 1

SELECT convert(decimal(15,2), '-3.56443E-35') 
    -> error: Error converting data type varchar to numeric.

SELECT try_convert(decimal(15,2), '-3.56443E-35') 
    -> NULL

SELECT convert(float, '-3.56443E-35') 
    -> -3.5644300000000002E-35

The are some Number functions in our expression language. toFixed() would convert number to a representation with a fixed number of digits after the decimal point, effectively 0 in this case.

Number Functions: https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439371/Number+Functions+and+Properties

[njahagirdar]

Hello Matthew,

Thank you for your inputs. I will try this and let you know if I get it right.

Regards,
Neeraj

[ForbinCSD]

Neeraj:

I have a short answer and a long answer.

Short answer: what version of SQL Server are you using? I don’t think -3.56443E-35 will fit in a DECIMAL(15,2). In versions prior to SQL Server 2016 (13.x), any float value less than 5E-18 that you tried to convert to such a DECIMAL would either throw an error, or round silently to zero, depending (AFAICR) on how you have the server or the connection configured. Later versions are smarter but you’d be better off reading the documentation than trusting in me.

Longer answer:
Unfortunately, many of today’s university programs are no longer teaching details of numeric representation in their classes for computer scientists or software engineers or developers. This is one of those cases where it can get us into trouble if we don’t understand the difference between common implementations of DECIMAL (or NUMERIC) and FLOAT / REAL / DOUBLE.

In this case, DECIMAL values in many implementations are stored as BCD values and computed long-form in software by Base-10 arithmetic. This is slower but prevents naively written mathematics from easily producing degenerate answers. In SQL Server, DECIMAL(15,2) means “store 15 digits total, and allow up to 2 of them to be to the right of the decimal point.” You could use this to store dollar amounts in the trillions. A number like 1,234,567,890,123.45 will fit in such a column or variable. A number like 0.12345 will be rounded to 0.12, and a number like 0.0000098765 won’t fit. (You’ll either get an error or it will round to zero, depending on a lot of different factors, i.e. MySQL will probably behave differently than Oracle.)

The implementation literally reserves enough space for 15 digits and decides where to assume the offset that the decimal takes.

Types such as FLOAT, REAL, or DOUBLE are floating point. They are good for navigation, graphics, and other mathematically intensive applications, but are not good for money. (If I have 10,925,379,656,943 Japanese Yen and you store it in a 7-significant-digit real, I can make 100,000 yen purchases over and over and over again and never affect my total.)

Floating point values are (on most 2’s complement processors) implemented as a binary mantissa and exponent pair. If I recall correctly, a SQL Server REAL has 24 bits of mantissa and 8 bits of exponent, which gives you roughly 7 significant digits in decimal, and a range of something like [+/-] 1.x E[+/-]38 to 3.y E[+/-]38 – you’ll have to look it up to find what x and y are, it’s not important to the point here. (What is important is that there’s a gap between the tiniest numbers and zero: this is where underflow happens.)

So, your value -3.56443E-35 might or might not fit in a SQL Server REAL, but it fits into a bigger type like FLOAT.

My big value 1,234,567,890,123.45 “fits” into a REAL, but not perfectly. It rounds to 1.234568E+12. If you convert the REAL back to a DECIMAL(15,2) you’d get 1,234,568,000,000.00, and the bank would be very angry you’d given me a free $109,876.55 if this was a banking application!

My very small value of 0.0000098765 does fit in a REAL, as 9.87650E-6. If you convert it to a DECIMAL(15,13), you’d get [0]0.00000987650 and all significant digits would be preserved.

I hope this long pedantic ramble has been of some use to either you or another reader, and hasn’t put you to sleep!! 😀

[njahagirdar]

Hello @ForbinCSD , thank you for your patience to give us all an insight into what it actually is. I really appreciate your patience and Yes it did not put me to sleep. I found a workaround to do this as the customer said the number is highly insignificant and we arrived at a common solution to tackle this. But thanks again for this elaborate explanation, I’m sure people will be benefitted from this answer.

Cheers!
Neeraj