I don't think its your convert function returning the error. Its your where giving the error. Try the convert in your where clause. Something like

where convert(numeric, ccfccard) between 10980000002 and 10990000000



-----Original Message-----
From: Johnson Shaunn [mailto:mssqldba-ezmlmshield-x71130526.[Email address protected]
Sent: Wednesday, March 30, 2005 15:38 PM
To: LazyDBA Discussion
Subject: example using cast or convert?

Howdy -

I'm trying to find correct syntax to convert a text column to
numeric. I've tried examples like so -


[snip examples]

select convert(numeric, ccfccard) from emp0
where ccfccard between '10980000002' and '10990000000'

**error: Server: Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type text to numeric is not allowed.

- also -

select convert(numeric, convert(varchar (50), ccfccard)) from emp0
where ccfccard between '10980000002' and '10990000000'

**error: Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

[/snip examples]


There was one attempt where I was able to get something like 'can't
convert
directly from text' (sorry, I forgot to copy the error message).

Can someone send me a doc to give examples of how to convert or cast
text fields into numeric format *without* having to change the database
/ field?

Thanks!

-X



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



MS Sql Server LazyDBA home page