Convert database table column from Money to Decimal

[SteveMac32]

Hi all
A rather simple problem but I have not touched SQL for 2 years and I am slowly getting back to it, so am a bit unsure of what could be simple problems.

I have a populated existing Money column in a table that now has to change to a datatype of Decimal(17,7)

Can I just do an Alter Table keeping the columns name and change the datatype? Will this have any affect on the data.

I am pretty sure this will be ok but am not 100% can anyone confirm this or have a better solution.


ALTER TABLE Account
ALTER COLUMN UnitPrice
Decimal(17, 7)


Thanks

Steve
Slowly getting back to sql

 

[gmmastros]

Steve,

That command should safely convert your money values to a data type of decimal. I tested this on a temp table and it worked just fine.

Create table #Account(UnitPrice Money)

Insert Into #Account Values(1234.2938)
Insert Into #Account Values(3498.239478)
Insert Into #Account Values(219384.9823948)
Insert Into #Account Values(2983498.92384)
Insert Into #Account Values(230498234.1239784)

Select * From #Account

Alter Table #Account Alter Column UnitPrice Decimal(17,7)

Select * From #Account

Drop Table #Account

I suggest that you first make a backup of your database before altering the column. It would also be better to run this on a production copy of the database instead of a live database (just to be safe).

-George

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

 

[SteveMac32]

Thanks George much appreciated