July 14, 2008 at 12:08 pm
I needed to change a data type from a money type to a varchar. Thne I was told that the change needed to be reversed yet I tried ALTER Table tableName ALTER COLUMN ColumnName money and it gives me an error trying to convert back. Is there another way I can do this?
July 14, 2008 at 12:11 pm
Is there data that violates the money datatype? You could always play the shell-game... create a new column of type money update the new column, drop the old column, then rename the new column back to the original name. I am sure that there are several potential pitfalls along the way like the column being a part of an index.
-- J
July 14, 2008 at 12:13 pm
I just tried this:
create table #T (
Val money)
insert into #t
select number
from dbo.numbers
alter table #t
alter column val varchar(100)
update #t
set val = '$' + val
alter table #t
alter column val money
And it worked.
That means you probably have some data in your column that can't be converted to the money data type. First, try using IsNumeric() to select any rows where the data isn't even numeric. I wouldn't be surprised if some of the rows have commas in them (thousands placeholder), which might mess up the conversion back.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 12:16 pm
Good calll, I had to delete the last four rows that had been inserted because they contained a %.
Thanks!
July 14, 2008 at 1:24 pm
Glad that worked out for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply