Data type conversion problem

Hi,
I have a variety of fields that I am trying to convert from Float to Decimal
for greater accuracy however we are having some problems converting them.
We use this fantastic tool called "SQL compare" by Red-Gate software except
that it's failing in one spot. So, we are now working with the remaining
fields that need to be converted field-by-field.
When we try to convert the field called dpst_amt it fails with:
Msg 8115, level 16, State 6, line 1
Arithmetic overflow error converting float to data type numeric.
Statment terminated
The values in [dpst_amt] field are numbers like 693.399999999998, if I try
[select round(dpst_amt,2)] the value never is rounded, if I try to copy the
value out to X field it fails as well. How did I determine what is in these
fields, meaning how do I find the bad record? One must be bad but where or
how do I find it?
Thank you!
ChrisWhat is the size of your decimal datatype? There must be some data in your
column that is larger than the largest that fits in the decimal datatype, so
if your decimal is for example decimal(6,2)
SELECT dpst_amt
FROM your_table
WHERE ABS(dpst_amt) > 9999.99
should give you the values that are outside the range of your decimal.
SELECT MAX(ABS(dps_amt)) FROM your_table
will give you an indication as to what the size of your decimal datatype
should be.
Jacco Schalkwijk
SQL Server MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
> Hi,
> I have a variety of fields that I am trying to convert from Float to
> Decimal for greater accuracy however we are having some problems
> converting them. We use this fantastic tool called "SQL compare" by
> Red-Gate software except that it's failing in one spot. So, we are now
> working with the remaining fields that need to be converted
> field-by-field.
> When we try to convert the field called dpst_amt it fails with:
> Msg 8115, level 16, State 6, line 1
> Arithmetic overflow error converting float to data type numeric.
> Statment terminated
> The values in [dpst_amt] field are numbers like 693.399999999998, if I try
> [select round(dpst_amt,2)] the value never is rounded, if I try to copy
> the value out to X field it fails as well. How did I determine what is in
> these fields, meaning how do I find the bad record? One must be bad but
> where or how do I find it?
> Thank you!
> Chris
>|||Originally the datatype was Float and what I've found since writing this was
the value of [1.#INF] was stored in the field. How does one search for that
value in the future?
Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eukytqyWFHA.2912@.TK2MSFTNGP10.phx.gbl...
> What is the size of your decimal datatype? There must be some data in your
> column that is larger than the largest that fits in the decimal datatype,
> so if your decimal is for example decimal(6,2)
> SELECT dpst_amt
> FROM your_table
> WHERE ABS(dpst_amt) > 9999.99
> should give you the values that are outside the range of your decimal.
> SELECT MAX(ABS(dps_amt)) FROM your_table
> will give you an indication as to what the size of your decimal datatype
> should be.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
>