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

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
Asked:
chickanna
1 Solution
 
knightEknightCommented:
what does TRUNCNUM do?  
0
 
Paul JacksonCommented:
SET @LoanToValue =  ROUND((((@LoanAmount + @LoanSubFinAmountCommon ) / @MinAmount)  + 0.0009),
                                  @LTVSignificantDigitsCalc) ;
0
 
Lee SavidgeCommented:
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



From: http://download.microsoft.com/download/7/C/2/7C20B070-BFF8-44B4-BD7D-1B03DF50F924/MigrateSybaseASAtoSQLServer2008.docx
0
 
Paul JacksonCommented:
Round works fine as a replacement as long as @LTVSignificantDigitsCalc is always a positive number.
0
 
Alpesh PatelAssistant ConsultantCommented:
Round is in SQL Server instead of TRUNCNUM
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now