|
|
Posted by Erland Sommarskog on 10/14/05 00:35
Mike (noway@forgetit.com) writes:
> Ok I have another problem with some of the data. I did not notice this
> but some of the columns where nvarchar data types and the math is not
> working on them. I tried to change the data type to decimal but
> Enterprise Manager is giving me and error saying it can not convert the
> data type.
>
> The table I am working was imported and the data types were nvarchar but I
> managed to change them to varchar data types but I need them to be numeric
> or decimal data types so that the math will work.
>
> Can any one tell how to change these?
One would guess that there is some junk data in that column that is
not convertible. (And which would explain the error you got when you
tried bulk insert.)
Try this for a start:
SELECT * FROM tbl WHERE isnueric(col) = 0
Review that data, and see if you can repair it.
Unfortunately, isnumeric() is not reliable, since it returns 1 if the
data can be converted to any numeric data type, so once you fixed those
with isnumeric = 0, you need to do refined checking. This is a possibility:
SELECT SUM(convert(decimal(28,14), col) FROM tbl
If this passes, you are fine. If it does not, you still have junk. If you
have a manageable number of service codes, you can do:
SELECT SUM(convert(decimal(28,14), col) FROM tbl WHERE SVCCODE = 'code1'
go
SELECT SUM(convert(decimal(28,14), col) FROM tbl WHERE SVCCODE = 'code2'
go
It's important that you run this in spearate batches, because the convert
error aborts the batch.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|