conversion explanation requested

  • Can someone point me to the documentation and or explain why I can not convert a varchar that contains values like .7, 2, 1.3 to decimal(3,1) but I can convert it to float and then to decimal(3,1)?

    Thanks.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • there must be more to the story;

    this works without an issue at all:

    With MyCTE (val)

    AS

    (

    SELECT '.7' UNION ALL

    SELECT '2' UNION ALL

    SELECT '1.3'

    )

    SELECT *,convert(decimal(3,1),val) FROM MyCTE

    could it be that you have data that is larger than 999.9 (so it won't fit in a decimal(3,1), or something that has more precision than 1 decimal place?( ie 333.14?)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Well Lowell, there must be, but I am more confused now than I was yesterday when I hit this issue. I am importing an excel file into table that is created during the import. I then try to load the data, which is nvarchar but I also tried as varchar, into another table where the column is decimal(3,1). Just to be certain I also tried "select convert (decimal(3,1), '1.2')" as a test. Allow I swear it failed yesterday, it is working fine now. As I said I am more confused now than before.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • right, but if you try SELECT convert(decimal(3,1),'333.14')

    you get a conversion error;

    that's what i'm thinking is the issue; other daata than the examples you posted are outside of the data boundary; 333.14 will not fit in the decimal 3,1; it would fit in a decimal 5,2 for example.,

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I see that part, but the error I was getting, which I should have mentioned in the beginning, was

    "Error converting data type nvarchar to numeric". And I don't think your example would explain how an intermediate conversion to float would make it all work.

    SELECT convert(decimal(3,1),convert(float, '333.14'))

    I appreciate your interest in helping me understand this.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • Tobar (3/26/2013)


    I see that part, but the error I was getting, which I should have mentioned in the beginning, was

    "Error converting data type nvarchar to numeric". And I don't think your example would explain how an intermediate conversion to float would make it all work.

    SELECT convert(decimal(3,1),convert(float, '333.14'))

    I appreciate your interest in helping me understand this.

    OK sorry:

    you really get this error in SSMS:

    SELECT convert(decimal(3,1),convert(float, '333.14'))

    Msg 8115, Level 16, State 6, Line 1

    Arithmetic overflow error converting float to data type numeric.

    the specific reason is the definition of Decimal(3,1);

    that says 3 significant digits TOTAL, and one of those digits is to the right of the decimal point.

    333.14 is five significant digits, and two to teh right of the decimal point; so it will not fit in the defined size.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for your help on this.

    <><
    Livin' down on the cube farm. Left, left, then a right.

Viewing 7 posts - 1 through 6 (of 6 total)

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