Post reply

SELECT CONVERT(float,'-') throws error

  • March 4, 2008 at 5:53 am

    #203822

    I happened to come across the question below in the QoDs (Jun 24, 2005):

    What results do you get from this?

    SELECT CONVERT(float,'-')

    GO

    SELECT CONVERT(int,'-')

    GO

    SELECT ISNUMERIC('-')

    Could someone tell me why the first query throws an error while the next two work fine?

  • March 4, 2008 at 6:20 am

    #784887

    I think, note that key word, it's because of a rounding error when doing an implicit conversion from the string to the float. The other two can do the implicit conversion with a round to zero.

    Someone PLEASE correct me if I'm wrong.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 4, 2008 at 6:38 am

    #784902

    I think you're right Grant. Certainly the error message from SQL 2000 is along the same lines. If you try to convert the string '-0' to a float it works. The others do implicitly convert to 0 by assuming the dash is a sign and the trailing zero is assumed.

    Of course,

    select convert(float, convert(int, '-'))

    works for obvious reasons 🙂

    Also the code

    select convert(decimal(6,3), '-')

    errors but with a strange reason: Arithmetic overflow error converting varchar to data type numeric.

    Go figure 🙂

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

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