Converting varchar to Money

16 Sep 2005 4:34 PM

Snake

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fails
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?

WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."

However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )

16 Sep 2005 4:55 PM

Aaron Bertrand [SQL Server MVP]

(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.

(b) if using convert(money, convert(float())) works, then what is wrong with
using that?

(c) STOP STORING NUMERIC VALUES AS STRINGS!

Show quote

"Snake" <Sn***@discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
>
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
>
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>
>

16 Sep 2005 5:13 PM

Snake

Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures.

I will go read the link you provided.

Thanks

Michael

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
>
> (b) if using convert(money, convert(float())) works, then what is wrong with
> using that?
>
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
>
>
>
> "Snake" <Sn***@discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@microsoft.com...
> >I have a a Case statement which sometimes fails when converting a Varchar
> > column which contains numeric values to Money. I can understand why it
> > fails
> > when "1.05E+07" is passed in, but other values appear to fail also. I
> > have
> > not located the other offending values yet (500,000 rows to sift through)
> > but converting them to Float first avoids the errors. Any ideas?
> >
> > WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> > "Server: Msg 235, Level 16, State 1, Line 1
> > Cannot convert a char value to money. The char value has incorrect
> > syntax."
> >
> > However the following code, which converts to Float, and then to Money,
> > does
> > not fail.
> > WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> > CONVERT(FLOAT,c1_PriorSalePrice) )
> >
> >
> >
>
>
>