April 30, 2008 at 11:55 am
as of now i have a field with money as datatype and that stores only 4 scale (decimal places)
but the user want to store upto 5 decimal places.
What data type can i use to achieve this and how do i do it without losing my existing data?
Please suggest
April 30, 2008 at 12:53 pm
You can switch over to the decimal data type and you shouldn't lose any of your data. You'll have to write a script that migrates it over course, but no actual conversions between money & decimal are required.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2008 at 1:14 pm
I just tested this:
create table #Test (
ID int identity primary key,
Cash money,
DecCash decimal(15,5))
insert into #test(cash, deccash)
select cast(n1.number as money) + cast(n2.number as money)/100.00,
cast(n1.number as decimal(15,5)) + cast(n2.number as decimal(15,5))/100.00
from common.dbo.numbers n1
cross join common.dbo.numbers n2
where n1.number between 0 and 10
and n2.number between 0 and 99
alter table #test
alter column Cash decimal(15,5)
select *
from #test
The "alter column" command worked just fine. Zero data loss/degredation. You'll have to define the decimal type you want, but it should be as simple as that.
- 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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply