Conversion of the varchar value error

1

(imported topic written by RosaMartin)

I’m running the following query and get the error message below - can you help me with this?

SELECT A.ActionID,

A.ComputerID,

A.FixletID,

A.Name,

A.Username,

A.StartTime,

DATEADD(s, CAST(BFEnterprise.dbo.fn_ExtractField(‘EndTime’, 0, D.Fields) AS integer), ‘1970-01-01’) AS ‘EXPIRATION_TIME’,

A.Sitename,

A.ActionStatus

FROM BFEnterprise.dbo.BES_ACTION_DEFS D RIGHT OUTER JOIN

BFEnterprise.dbo.BES_ACTIONS A ON D.ID = A.ActionID

WHERE (D.ParentID != 1)

AND (BFEnterprise.dbo.fn_extractfield(‘EndTime’,0, Fields) is NULL

OR ((DATEADD(s, cast( BFEnterprise.dbo.fn_extractfield(‘EndTime’,0, Fields)as integer) , ‘1970-01-01’))) > getdate() )

ERROR:

Msg 248, Level 16, State 1, Line 1

The conversion of the varchar value ‘2832414266’ overflowed an int column. Maximum integer value exceeded.

2

(imported comment written by BenKus)

Looks like the numbers you are querying are bigger than a 32-bit integer… Maybe try replacing “as integer” with “as bigint”

Ben

3

(imported comment written by RosaMartin)

I changed integer to bigint and got the following error

Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

4

(imported comment written by BenKus)

Hmmm… I am not sure what the issue is there… maybe it can’t do a dateadd with such a big number…

Although it appears from that number that one of your actions expires in “04 Oct 2059”… If you can stop/remove that action, it should remove your problem…

Ben