• Status: Solved
• Priority: Medium
• Security: Public
• Views: 425

SQL server equivalent

experts.. I have to migrate an old sybase database in windows to sql server 2008. There is a function called TRUNCNUM in sybase. Is there an equivalent function in sql server 2008. If not, how do I convert this code to work in sql server 2008

SET @LoanToValue =  TRUNCNUM((((@LoanAmount + @LoanSubFinAmountCommon ) / @MinAmount)  + 0.0009),
@LTVSignificantDigitsCalc) ;
0
chickanna
1 Solution

Commented:
what does TRUNCNUM do?
0

Commented:
SET @LoanToValue =  ROUND((((@LoanAmount + @LoanSubFinAmountCommon ) / @MinAmount)  + 0.0009),
@LTVSignificantDigitsCalc) ;
0

Commented:
TRUNCNUM (numeric-expression, integer-expression)
Truncates a number at a specified number of places after the decimal point.

Sybase ASA example:
truncnum ( 655, -2 )
truncnum ( 655.348, 2 )

Solution:
In SQL Server, use the following CASE construction. Also you can implement this function as a user-defined function.

SQL Server example:
CASE
WHEN 655>=0 THEN
FLOOR(655*power(CAST(10 AS float), -2))/power(CAST(10 AS float), -2)
ELSE
CEILING(655*power(CAST(10 AS float), -2))/power(CAST(10 AS float), -2)
END

CASE
WHEN 655.348>=0 THEN
FLOOR(655.348*power(CAST(10 AS float), 2))/power(CAST(10 AS float), 2)
ELSE
CEILING(655.348*power(CAST(10 AS float), 2))/power(CAST(10 AS float), 2)
END