Change datatype money to decimal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Change datatype money to decimal

Author

Topic

jh_sql
Starting Member

24 Posts

Posted - 2009-08-12 : 02:32:58

Hey,

I have situation that i need to change existing datatype, money to decimal as i need to save 6 decimals and money only allows 4. Does anyone have experience if there might be any data loss? current table has 70 000 rows, wich all should stay as they were.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-08-12 : 12:00:02

You shouldnt lose any data.. if you are not sure, add a new column with the new datatype, update it with the values form your current money type values and write some queries to see if all data matches.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 12:06:21

Your DECIMAL or NUMERIC datatypes should probably be scoped to allow the full range of possible MONEY values. These are:
money
-922,337,203,685,477.5808 to 922,337,203,685,477.5807

smallmoney
- 214,748.3648 to 214,748.3647

So for Money you want at least a 21 wide, 6 precision type. I'd go for 22 to be safe.

NUMERIC(22,6) would probably do the job.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jh_sql
Starting Member

24 Posts

Posted - 2009-08-17 : 02:40:35

Okay, thanks for replies :)

Opened up new idea aswell, i'll just make new column, update existing data to that column, rename old, and do the datatype change. Incase something goes wrong, i can just switch old column back :P

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 03:40:38

Note that the new column cannot have same name as old column, so after the update thingy and testing, you need to drop the old column and rename the new column to the name of the old column.

Or

BEGIN TRAN

ALTER Table1 ALTER COLUMN OldColumn DECIMAL(21, 6)

IF @@ERROR <> 0 ROLLBACK TRAN
ELSE COMMIT TRAN

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources