Help changing data type - SQL Server Forums

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 2008 Forums

Other SQL Server 2008 Topics

Help changing data type

Author

Topic

ShiftySid
Starting Member

5 Posts

Posted - 2009-01-11 : 14:44:42

I have created a table - tblClients which contains a field called 'Fee'. Unfortunately, this was created as nvarchar rather than numeric. I want to change it to numeric (10,2) but because it contains data - it won't allow the conversion.

There are only 300 rows, but still more than I'd want to type out from scratch. The data only contains figures in two-digit format, for example £15.00 is simply '15'. How can I convert this field to numeric and retain the exisiting data?

I've tried exporting to Excel, deleting table data, changing structure and re-importing the data but this failed.

Also tried creating a new table with the correct structure and copying the data over but this failed too.

Any magical ways this can be done please?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 15:24:08

How can you store £15.00 in numeric(10,2)? If it was 15 you would able to convert it to numeric(10,2). You can let front end handle £ and $ sign if you want only put numeric entry.

ShiftySid
Starting Member

5 Posts

Posted - 2009-01-11 : 17:33:04

Sorry didn't mean £15.00. It was in the nvarchard field as simply '15' - I needed it as numeric '15.00'.

I cheated in the end. I created a new column called Fee2, used VB to open both columns and a FOR loop to copy the data from one column (convert to string, appending .00 and pasting into the second column) and saving it.

Fee2 was created as numeric. I then deleted column Fee, renamed Fee2 to Fee and Bob's your uncle.

Not ideal, but the ends justified the means.

revelator
Starting Member

32 Posts

Posted - 2009-01-13 : 05:09:33

What you could have done (and should do in the future to save doing it in VB) is to extract the data to a temp table and convert the data type. The drop and re-create the original table with the new datatype, and insert the rows from the temp table. Simplified example below...


create table Conversion
(
Amount nvarchar(10)
);
go

insert into Conversion
(Amount)
values
('15'),
('199'),
('4');

create table #conv
(
Amount numeric(10,2)
);
go

insert into #conv
(Amount)
select cast(Amount as numeric(10,2))
from Conversion;

drop table Conversion;

create table Conversion
(
Amount numeric(10,2)
);
go

insert into Conversion
(Amount)
select Amount
from #conv;

select * from Conversion;

-----------------------------------
Waiting for the Great Leap Forwards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources