October 31, 2011 at 4:18 am
Hi Friends,
I am facing an problem like "Arithmetic overflow error converting numeric to data type numeric."
I have an table variable and the sum of column is '1029097.95790000' Datatype is NUMERIC(38,8)
what i am trying to do is to insert the above result of the table variable to the final table but the final table column Datatype is NUMERIC(11,6)
how is it possiable can any one help me on this
Need help? Help us help you.
October 31, 2011 at 4:45 am
The precision and scale of your datatype is not large enough to support that number at that accuracy.
Your current dataype can support Five digits in front of the decimal and Six after so you can either change the datatype or change the precision of the number, but be aware that you will be losing accuracy.
October 31, 2011 at 5:26 am
Tks for ur rply is it possiable to use cast or convert function their...
Need help? Help us help you.
October 31, 2011 at 5:38 am
steveb. (10/31/2011)
The precision and scale of your datatype is not large enough to support that number at that accuracy.Your current dataype can support Five digits in front of the decimal and Six after so you can either change the datatype or change the precision of the number, but be aware that you will be losing accuracy.
How could i change the precision of the number....Is their any otheir way to load
Need help? Help us help you.
October 31, 2011 at 5:57 am
tommey152 (10/31/2011)
steveb. (10/31/2011)
The precision and scale of your datatype is not large enough to support that number at that accuracy.Your current dataype can support Five digits in front of the decimal and Six after so you can either change the datatype or change the precision of the number, but be aware that you will be losing accuracy.
How could i change the precision of the number....Is their any otheir way to load
Change the data type.
That number is never going to fit in that datatype as you have 7 digits in front of the decimal and your datatype will only allow 5.
so either change the datatype or change the number
October 31, 2011 at 6:01 am
either change the column so that its the same as the original column being imported, or convert the value on the insert, but make sure that when you convert that the values which come out are the values you want as it will round up or round down depending on the values, also check with the business that the values should be rounded up or down and not left up to SQL to decide as you may have to write some code to always round up or always round down
October 31, 2011 at 6:28 am
I would start looking for
select ... from sourcetable where yourcol > 99999.999999
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 31, 2011 at 6:40 am
Thanks 4ur rply guys......What i need is to load it like this '1029.9579' is their any way help me
Need help? Help us help you.
October 31, 2011 at 6:51 am
to move the decimal place divide or multiple by a factor of 10..
eg
select CAST( round((1029097.95790000/1000),5) as numeric(10,6))
October 31, 2011 at 11:38 pm
Sry apologize me.....What i need is to load it like this ''1029097.9'' is their any way help me
What i xactly need is to remove all the fields after the decimal, but infront of the decimal should not be changed...
Need help? Help us help you.
November 1, 2011 at 2:59 am
tommey152 (10/31/2011)
Sry apologize me.....What i need is to load it like this ''1029097.9'' is their any way help meWhat i xactly need is to remove all the fields after the decimal, but infront of the decimal should not be changed...
no. You declared the column. 11,6 so, Max 5positions before the decimal point.
Your alternative is to modify the column definition.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply