MONEY Data Type Conversion

Question

We have a few of our Database Columns defined with the MONEY Data Type.

We don't want to go down a rabbit hole and step on any land mines here.

What all is involved for converting an existing Database Table and its data columns defined as the MONEY Data Type?

Do we have to manipulate things around to Data Pump and Convert the existing Data to a Staging Table and then DROP and Re-CREATE the existing Table enhancing for the New Non-MONEY Data Type and what should the Data Type be defined as?

Is there any easy way to convert the Data Column Data Types and the data will be alright?

Answer 1

Hi BobbyP-1695,

You can try to convert money data type to decimal(19,4).
And you can try to add a new column, copy existing data to that column, rename the old column, and change the data type. In case something goes wrong, you can switch the old column back.
Please refer to https://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale which might be helpful.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Why do you want to change the datatype from Money to something else?

As long as you are converting it to a compatible data type, you can simply change the data type in place.

See:
https://learn.microsoft.com/en-us/sql/relational-databases/tables/modify-columns-database-engine?view=sql-server-ver15

Answer 3

The simplest way is to say

ALTER TABLE tbl ALTER COLUMN moneycol decimal(20,2) NOT NULL

The caveat is that this is an order-of-data operation so the table has to be rebuilt. If this is a 500 GB table that can be painful, and in that case, you may have to look for options.

Also, you will need to make a decision on data types. Above I only typed what came into my mind. Since money supports four decimals, you could lose some data if you go by my suggestion.