locked
Decimal (18,5) to NUMERIC (15,6) Conversion RRS feed

  • Question

  • Hi All,

    I am facing a small issue where :

    Source column is - DECIMAL (18,5)

    Destination column is - NUMERIC(15,6)

    I have below sample values to convert

    1799102776.32000

    1429547589.78000

    4014201193.92000

    Now this is definitely not good as we may land into a case where at source we do have precision values upto 18 and it will be hard to accommodate it into numeric 15 precision but as per our knowledge of application and business there will not be any such case/value.

    Regards


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Tuesday, April 9, 2019 12:45 AM

All replies

  • You cannot,

    decimal[ (p[ ,s] )]
    The maximum total number of decimal digits to be stored. 
    This number includes both the left and the right sides of the decimal point. 
    The precision must be a value from 1 through the maximum precision of 38.

    select try_cast(1799102776.32000 as decimal(15,6))  --null
    select try_cast(1429547589.78000 as decimal(15,6))  --null
    select try_cast(4014201193.92000 as decimal(15,6))  --null

    Tuesday, April 9, 2019 1:14 AM
  • Yes, let's use the sample data "1799102776.32000" to explain the reason why it can't be converted into expected data type.

    The left side of decimal point is 1799102776, and it has 10 digits, but the data type "NUMERIC(15,6)" could only store 15-6=9 digits. When you use cast function to convert it, you would get "Arithmetic overflow error" message.

    Further more, if you insist on doing that, it would lead to loss precision.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 9, 2019 2:57 AM