SQL server equivalent

Posted on 2011-05-10
Last Modified: 2012-06-21
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) ;
Question by:chickanna
    LVL 33

    Expert Comment

    what does TRUNCNUM do?  
    LVL 29

    Expert Comment

    by:Paul Jackson
    SET @LoanToValue =  ROUND((((@LoanAmount + @LoanSubFinAmountCommon ) / @MinAmount)  + 0.0009),
                                      @LTVSignificantDigitsCalc) ;
    LVL 25

    Expert Comment

    by:Lee Savidge
    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 )

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

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

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

    LVL 29

    Accepted Solution

    Round works fine as a replacement as long as @LTVSignificantDigitsCalc is always a positive number.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Round is in SQL Server instead of TRUNCNUM

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now