Post reply

display numbers before decimal without rounding

  • August 30, 2011 at 10:42 am

    #96427

    If i have 99.6513 i would like to see

    99

    If i have 0.666666I would like to see

    0

  • August 30, 2011 at 10:45 am

    #1375128

    FLOOR(99.6513)

    FLOOR (0.666666)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 30, 2011 at 10:49 am

    #1375131

    Thanks, that worked

  • August 30, 2011 at 10:52 am

    #1375133

    Be careful with floor. Depending on how you understand the requirement, it will return the expected answer for positive numbers, but NOT for negatives.

    It you're going from float or decimal to int, CAST may be the best way to do it. Casting numeric to INT is a truncate operation (which is what I understand that you want).

    select CAST(n as int) ,FLOOR(n)

    from

    (select 99.6513 as n

    union all

    select 0.666666 as n

    union all

    select -99.6513 as n

    union all

    select -0.666666 as n

    ) testcte

    Edit: fixed the test case for SQL 2000.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply