Hi,
I have a char field that I need to convert to bigint. I get an arithmetic overflow error. How can I find the values causing the problem? The table is over 200,000 rows.
Thanks
As a first try maybe find where there are non digits
I think.
Doesn't return any rows
See if there are any numbers that are too large. BIGINT has 19 digits:
SELECT * FROM YourTable
WHERE LEN(REPLACE(YourColumn,'-','')) > 19
Not all 19 digit numbers can be expressed as bigint, so if you still don't find anything, convert the string to float and see if it is greater than 2^63-1 or less than -2^63
1 Like
I now get results:
08754654400416187200
08760009500264266700
05070009500120086500
91950023800002860450
The first 3 are ok because if you remove the leading zero, they are within the range of BIGINT. But the fourth one that starts with 9 has 20 digits and is too large to be stored as a bigint. That will give you an arithmetic overflow.
-- this will succeed.
SELECT CAST('08754654400416187200' AS BIGINT);
-- this will cause arithmetic overflow
SELECT CAST('91950023800002860450' AS BIGINT);
The largest bigint value allowed is 9,223,372,036,854,775,807 (19 digits). Thus, you can find values that are too large like this:
SELECT *
FROM table_name
WHERE CAST(char_field AS decimal(28, 0)) > 9223372036854775807
If you can have negative values, you'll need to add a check for that as well:
WHERE NOT (CAST(char_field AS decimal(28, 0)) BETWEEN -9223372036854775808 AND 9223372036854775807)
1 Like
Guys thanks so much. Love this forum and always the same people helping me. Thank you 
What math are you doing with something lie that? 
Also, look up what "field" means in SQL; you meant "column" instead.