Decimal (18,5) to NUMERIC (15,6) Conversion

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 ++++

Answer 1

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

Answer 2

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 [e-mail].