alter a column in a table from money to varchar

[kodaksmile]

Needing a little help....

What is the best way to alter an existing column from a money data type to varchar?

Thanks

 

[AlexCuse]

Opening the table in design view and changing the data type is probably the easiest, unless you need to do this on a regular basis.

I can't imagine why you would need to do it on a regular basis, so I'll stick with that.

A wise man once said
"The only thing normal about database guys is their tables".

 

[kodaksmile]

The problem is I need to script the change so I need an alter table alter column script however, the conversion between money and varchar is not implicit.......

 

[SQLBill]

One option would be to add a new column of varchar. Update the rows to copy the money value into the varchar column, then drop the original column.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Normally...

Alter Table [!]TableName[/!] Alter Column [!]ColumnName[/!] VarChar(20)

But, since you cannot implicit convert from money to varchar, you will need to...

1. Add a new field
2. Update the new field with the explicitly converted money field
3. Drop the original column
4. rename the new column to the original column

Ex...

Alter Table [!]TableName[/!] Add  TempField VarChar(20)
go
Update [!]TableName[/!] Set TempField = Convert(VarChar(20), [!]MoneyField[/!])
go
Alter Table Temp Drop Column [!]MoneyField[/!]
go
sp_rename '[!]TableName[/!].TempField', '[!]MoneyField[/!]', 'COLUMN'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Bill,

You caught me snoozing....

Kodaksmile,

This will probably work out ok for you, but you may have problems if the MoneyField column is a foreign key to another table.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kodaksmile]

Thanks guys,

I had just tried the adding of a column and updating when I saw your post. For some crazy reason I was thinking I could use cast like you would in the select in the alter. Never quite as simple as you would like it to be.

Thanks for the help !!!!

 

[ESquared]

Go make the change in enterprise manager. Instead of saving, display the script.

Voilà!

It will do basically the following steps (not sure of order):

• Create a new table with the correct columns, defaults and other non-referential constraints
• Copy data from the old table to the new table, transforming any columns
• Drop constraints and indexes from old table
• Delete old table
• Rename new table to original table name
• Add referential constraints to table
• Add permissions and extended properties