February 2, 2008 at 9:02 pm
i tried the GUI first thing when trying to convert the
datatype from float to decimal (25,10)
it errored out with the following in just about a minute:
"Arithmetic overflow error converting float to data type numeric."
as far as i'm concerned any values after 10 digits to the right of the decimal
can be lost 'truncated' while the conversion is taking place.
unfortunately; i can't get it to complete without getting this error.
too bad though; GUI would be so much easier.
February 2, 2008 at 10:32 pm
Make it decimal(38,19). After that, do some table update of sorts to round the data appropriately and then use your GUI tools to make it decimal(25,10).
The table update could be something like
update myTable
set myCol = round(myCol, 10, 1)
The 10 indicates 10 decimal places. The 1 (or anything apart from 0 actually) indicates to truncate rather than round.
You could also do these steps
1. Use the GUI to add a new column of decimal(25,10) called X
2. Issue this statement
update myTable
set X = round(oldColumn, 10, 1)
3. Use the GUI to delete the old column and rename the new column appropriately
Good luck
February 2, 2008 at 10:50 pm
it's interesting that you posted information about the 'round' function. i
was already running a few tests with it and was about to ask about using 'round' in a
conversion script.
i'm checking into this now, and will post an update on that.
thanks 🙂
February 3, 2008 at 2:34 am
added the new precision no problem,
then truncated using round(mycolumn, 10, 1) no problem.
then changed the datatype to Decimal (25, 10) using
the alter table - alter column command, and... after
a long wait. it failed with the same error.
it's kind of late over here so i'll pick this up in the morning.
thanks for all the useful feedback so far.
February 3, 2008 at 12:39 pm
Hello simsql,
as already mentioned by others, you need to use a greater precision.
You wrote that there are the following values available:
5.87348091654376E+15
8.56784091067654E+15
7.58674490270345E+15
3.56734927376573E+15
Take the first one:
5.87348091654376E+15
In decimal notation this is
5873480916543760
If you count the digits, you will see that this value has 16 digits.
But decimal(25,10) allows only 15 digits before the decimal point, therefore you get the arithmetic overflow.
What you need is decimal(x,y) where x >= 16 + y. An example would be decimal(26,10)
Make sure you check BOL if you do not understand the decimal datatype yet.
Best Regards,
Chris Büttner
February 3, 2008 at 7:10 pm
Ian,
all failed with the same error. i want to try something like duplicating the
table, then run an import but transform the data on the way over to the
other table. not sure if that would make a difference though.
worth a shot i guess.
Christian,
thanks for the post. i understand... in this case i'm trying to get the
values not to become simply a decimal(x,y), but to line up with a particular
decimal precision & scale for another application. i'm able to convert
to a decimal, but not for the precision & scale that is needed.
the application which needs this data (of which i have no control over)
requires it to be (25, 10). it's been tough.
any digits after round(MyCol, 10) can be truncated, and even that
works fine, but then when applying the new datatype it still fails
with the same error. it's bizzarre
February 3, 2008 at 11:06 pm
simsql,
You need to do a conversion and round to the nearest whole number. From your post it seems like have a E15 is your largest float. If it is then you can use the following script. It will only convert the rows that have an exponential value of 15 or greater. Please note that if you have float values with an exponential value > 15; you will have to modify the script by adding more clauses to the case statement.
/*
This method assumes that the
largest value has an exponential value 15
you will need to add more case statements,
if this is not true.
*/
SELECT
--If the float column has a percision > 15 then convert
CASE WHEN CHARINDEX('.',CONVERT(DECIMAL(26,10),MyFloatColumn)) >= 17 THEN
--This method will put the last digit in the decimal place
--and if the last digit is > 5 will round to the next whole #
ROUND(CONVERT(DECIMAL(25,10),CONVERT(DECIMAL(26,10),MyFloatColumn)/10),0)
ELSE
CAST(MyFloatColumn AS DECIMAL(25,10))
END AS [Decimal],
MyFloatColumn AS [Float]
FROM MyTable
February 4, 2008 at 12:44 am
Hello simsql
If the application requires the data in 25,10 then you can only store numbers
up to 999999999999999.9999999999 in your application.
All values in your database greater than this value need to be "truncated" to the smaller value.
SELECT CASE WHEN CAST(@myfloat AS decimal(38,10)) >= 999999999999999.9999999999
THEN CAST(999999999999999.9999999999 AS decimal(25,10))
ELSE CAST(@myfloat AS decimal(25,10)) END
Just be aware of the fact that all big numbers exceeding the decimal(25,10) precision are incorrect after conversion.
Best Regards,
Chris Büttner
February 4, 2008 at 9:52 am
thanks for the feedback on that, but doesn't
this do the same thing?
update myTable
set myCol = round(myCol, 10, 1)
February 4, 2008 at 10:56 am
thanks for the feedback on that, but doesn't
this do the same thing?
update myTable
set myCol = round(myCol, 10, 1)
No. The reason it is not the same is because when you use the round function, you still maintain the same number of digits, only everything else gets changed back to zero. The problem you are facing is a percision problem. You need a percision of 26 to accomodate some of your data, but your business requirements state that you have to make the column fit into a decimal(25,10). With that said, you must remove an extra digit from the back of your float column to make it fit. For example, 5873480916543760 is 16 digits long plus your have an additional 10 for scale, thus you need (26,10). You need to make 5873480916543760 look like 587348091654376 and you need to account for the rounding of the truncated digit. When you use the round(mycol, 10, 1) you will see that your column is still 5873480916543760.
Your options are to remove the last digit of your float column by using my script or to apply the maximum float value for a decimal(25,10), for these columns as Christian suggested.
February 4, 2008 at 11:19 am
Wow... It's unreal to how knowledgeable you guys are in this stuff.
I'm looking into the former script to see If I can get it to run properly.
I'm getting a declaration error; so I'll need to work that out, and
point it to the table.
Thanks again for all the response. It's a big help 🙂
February 4, 2008 at 1:00 pm
Adam, Thanks my friend.
So now; I can see the values both Decimal, and Float,
and it got through no problem. I'm so used to seeing errors,
that I'm amazed it accomplished.
Would it be wise to now change the Datatype
manually? I checked the table design, and noticed
the type is still set to float.
February 4, 2008 at 1:29 pm
Would it be wise to now change the Datatype
manually? I checked the table design, and noticed
the type is still set to float.
If I am understanding your correctly, you are saying that the new column you created has a FLOAT datatype, instead of DECIMAL(25,10) and you now want to change the column to a DECIMAL(25,10) using the SSMS GUI?
If this is what you are saying, there should be no problem in using the GUI to do the change.
February 4, 2008 at 2:04 pm
well.. prior to all this my first attempt was to change the datatype
from float to decimal (25,10) however couldn't get passed the arithmetic
overflow error. not only from gui, but also using the tsql alter table,
alter column MyCol (25,10) statement did not work.
the script you provided was the first thing from all of this thread which
was able to convert the data properly. now all i need to do is somehow
make it work while converting the column to decimal.
does that make sense?
i can use an example given before from Christian where i simply add an
extra decimal (25,10) column, incorporate your script as a 'select into'
thus populating the column with the right type of values, and simply
remove the original column that is almost impossible to convert otherwise.
February 4, 2008 at 5:28 pm
This is actually much easier than you think just run the script as I have it below. This method works because you are converting all the floats to decimal(25,10) thus they will all convert successfully.
/*
This method assumes that the
largest value has an exponential value 15
you will need to add more case statements,
if this is not true.
*/
UPDATE MyTable
--If the float column has a percision > 15 then convert
SET MyFloatColumn =
CASE WHEN CHARINDEX('.',CONVERT(DECIMAL(26,10),MyFloatColumn)) >= 17 THEN
--This method will put the last digit in the decimal place
--and if the last digit is > 5 will round to the next whole #
ROUND(CONVERT(DECIMAL(25,10),CONVERT(DECIMAL(26,10),MyFloatColumn)/10),0)
ELSE
CAST(MyFloatColumn AS DECIMAL(25,10))
END
FROM MyTable
GO
ALTER TABLE MyTable
ALTER COLUMN MyFloatColumn DECIMAL(25,10)
GO
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply