CODEWITHSUNDEEP
sqlsql-servercasting
feetwet
feetwet

Reputation: 3446

Why does MS SQL CAST as bigint result in int overflow?

Why does the following T-SQL statement:

select CAST(COALESCE('3537601039',0) as bigint)

result in the following error when run against MS SQL Server 10?

The conversion of the varchar value '3537601039' overflowed an int column.

Is there a "more correct" way to do this conversion of a varchar to a bigint that would avoid this problem when the number is within range of a bigint?

Upvotes: 1

Views: 2423

Answers (3)

IVNSTN
IVNSTN

Reputation: 9318

Cast first, then do ISNULL

select ISNULL(CAST('3537601039' as bigint), 0)

select ISNULL(NULLIF(ISNULL(CAST('3537601039' as bigint), 0), 0), 1) + 3537601039

sql server firstly discovers isnull(string, numeric) and that numeric zero is by default int. So it tries to convert that string containing bigint to int. You were trying to cast to bigint too late.

another try:

select ISNULL('3537601039', 100000000000)

Upvotes: 2

Joe C
Joe C

Reputation: 3993

The first thing that is happening is that your string is trying to convert to a regular int to match the 0. Try this:

select CAST(COALESCE('3537601039','0') as bigint)

Upvotes: 2

feetwet
feetwet

Reputation: 3446

It appears that putting the unqualified zero in the coalesce is implying a conversion to a smaller int before it is explicitly recast to a bigint.

The following simple change to make it a coalesce of string values solves the problem:

select CAST(COALESCE('3537601039','0') as bigint)

Upvotes: 2

Related Questions