virkboy virkboy - 4 months ago 8
SQL Question

Arithmetic overflow error for type int

I'm currently getting this error when do a select on a table

Arithmetic overflow error for type int, value = 4318826193.000000.

I'm trying to figure out what column is causing this , what is the best way to figure out what column is causing this problem with this value 4318826193.000000 ?

I've tried writing a select statement with all the INT columns with a CAST( [column name] as bigint) but the error still appears.

SELECT
CAST(Id AS BIGINT)
,CAST (CreatedBy as BIGINT)
,CAST (UpdatedBy as BIGINT)
,CAST (HeaderId as BIGINT)
,CAST (DiskFileId as BIGINT)
,CAST (Status as BIGINT)
,CAST (TotalLines as BIGINT)
,CAST (CompletedLines as BIGINT)
,CAST (ProcessedLines as BIGINT)
,CAST (PercentComplete as BIGINT)
,CAST (SecondsRemaining as BIGINT)
,CAST (RollbackUser as BIGINT)
,CAST (CreatedRecords as BIGINT)
,CAST (UpdatedRecords as BIGINT)
,CAST (SkippedRecords as BIGINT)
,CAST (DiallerExportProgress as BIGINT)
,CAST (idxCampaignId as BIGINT)
,CAST (ScheduleId as BIGINT)
,CAST (CampaignId as BIGINT)

from [table_name]


Thanks

Answer

Use Try_convert,For conversion failures instead of erroring out ,TRY_Convert Throws null

select try_convert(int,4318826193.000000)

How to use..

Select TRY_CONVERT(BIGINT,DiskFileId)