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

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.