can casting bigint into varchar(20) truncate or overflow.

Question

Dear all

i have a table and a column

create table t1 ( col1 bigint)

ex: select cast(col1 as varchar(20))  as eid from t1

this query is working fine for -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

my doubt is there any situation (setting) where varchar(20) needs to be extended?

yours sincerlly.

Answer 1

I don't think so. But if you change it into varchar(32) you can surely sleep without overflow worries.

SQL number types:

http://www.sqlusa.com/bestpractices2008/sql-number/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 2

Hello,

it should word well. Did you experience any problem?

DECLARE 
 @bigint_low BIGINT = -9223372036854775808
,@bigint_high BIGINT = 9223372036854775807
,@varchar_low VARCHAR(20) 
,@varchar_high VARCHAR(20)

SET @varchar_low = CAST(@bigint_low AS VARCHAR(20))
SET @varchar_high = CAST(@bigint_high AS VARCHAR(20))

SELECT @varchar_low AS VARCHAR_LOW, @varchar_high AS VARCHAR_HIGH