Handle Truncate Exception with T-Sql

A collegue in RBC asked me to help with his truncate exception within his very complicated Sybase reporting query.

The code that causes truncate exception and terminates execution looks like the following:

SELECT POWER(1.09000000000012, 1/65559)*1.00006

Obviously, the truncate is caused by precision setting applied implicitly. For numeric types, such as decimal, numeric, int, bigint, float, real, double, Sybase has different precision scale on them. For example, decimal value can be specifed with explicit precision when declare it as DECIMAL(precision, scale) - scale is the digits after the dot. The precision is total digits including before and after the dots. By default, decimal value has a precision of 18 digits, but can be specified less than 38 digits.


To solve the problem, We need to CAST or CONVERT the partial value data type so that make the result of an operation within the boundary. In the end, the following CAST solve his problem:

SELECT (CAST POWER(1.09000000000012, 1/65559) AS FLOAT) * 1.00006