Post reply

Floor(), strings, a precision limits

  • March 5, 2014 at 8:01 am

    #151326

    It seems SQLServer draws a line at a given precision (I guess it's 16) for the floor() function when interpreting strings that 'look' like reals... it starts to round up:

    --Not that this should ever happen, but:

    print @@version;

    declare @x integer;

    set @x = floor('43.999999999999999');

    print @x;

    set @x = floor('43.99999999999999');

    print @x;

    --set @x = floor('43.99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999');

    --print @x;

    --results in 44

    --What's the deal? The first rounds up, the second truncates as one would expect of floor().

    Output:

    Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)

    Dec 10 2010 10:56:29

    Copyright (c) 1988-2005 Microsoft Corporation

    Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

    44

    43

    So... what's the deal? I'd never personally allow stringage to get to my math functions, but... hmm.

  • March 5, 2014 at 8:13 am

    #1694324

    SJTerrill (3/5/2014)

    It seems SQLServer draws a line at a given precision (I guess it's 16) for the floor() function when interpreting strings that 'look' like reals... it starts to round up:

    --Not that this should ever happen, but:

    print @@version;

    declare @x integer;

    set @x = floor('43.999999999999999');

    print @x;

    set @x = floor('43.99999999999999');

    print @x;

    --set @x = floor('43.99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999');

    --print @x;

    --results in 44

    --What's the deal? The first rounds up, the second truncates as one would expect of floor().

    Output:

    Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)

    Dec 10 2010 10:56:29

    Copyright (c) 1988-2005 Microsoft Corporation

    Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

    44

    43

    So... what's the deal? I'd never personally allow stringage to get to my math functions, but... hmm.

    It isn't FLOOR that is causing is. It is the conversion to a real or float.

    select CAST('43.999999999999999' as real),

    CAST('43.999999999999999' as float),

    FLOOR(43.999999999999999)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 5, 2014 at 8:21 am

    #1694329

    Ah. So an implicit conversion before the function call. I haven't spotted that in the docs.

    Thanks, though.

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

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