I am trying to run below statement in Sybase 15.5, but I am getting "Arithmetic overflow occurred " error:
declare @TradeId BIGINT
select @TradeId=(20170103-19950000)*10000
select @TradeId
Please advise anything I am doing wrong.
Add a comment
|
3 Answers
The data type of constants in an expression is determined by their value. Since all predicates are seems to be a simple INT, the DBE uses INT to store them.
Cast one or all of them to BIGINT explicitly.
Comments
The 19950000 is assumed to be INT, which is why you are getting the error message. Explicitly cast it to BIGINT like this:
declare @TradeId BIGINT
select @TradeId=(20170103-CAST(19950000 AS BIGINT))*10000
select @TradeId
Comments
@TradeId is large enough (BIGINT is more than enough), however the constants are being interpreted incorrectly as INTEGERs, and Sybase expects the result to be an integer also, which is too small, so you're getting the overflow.
declare @TradeId BIGINT
declare @D1 BIGINT
declare @D2 BIGINT
select @D1 = 20170103
select @D2 = 19950000
select @TradeId=(@D1 - @D2 )*10000
select @TradeId
Should work.
You could also keep the current expression and do a CAST inline.
