Thursday, March 7, 2019

Arithmetic overflow error converting numeric to data type numeric

I had encountered a while ago and had completely forgotten how the CONVERT(DECIMAL(p,s), 'hopefully some number') works.

Arithmetic overflow error converting numeric to data type numeric

Basically in DECIMAL(p,s), p (precision) equals to the number of digits on left of the decimal point and to the right hand side as well. s (scale) is the number of digits you need after the decimal point (right hand side)

Example: SELECT CONVERT(DECIMAL(10, 6), '2003201.8561484918793503') will return this error has the total number of digits to the left and right hand side to the decimal is of course more than 10 (which is our value of p)

For successfully converting the given value to decimal with 6 digits to the right is when p = 23

SELECT CONVERT(DECIMAL(23, 6), '2003201.8561484918793503') gives 2003201.856148

Google is my friend

No comments:

Post a Comment