Change varchar data type of columns to real/ decimals

• mic.con87

  SSCommitted

  Points: 1947

  More actions

  March 25, 2014 at 4:30 am

  #283762

  Hi,

  I've imported text files into SQL server and by default the fields are varchar(50) data type.

  I'm trying to alter certain columns but I keep getting an error. Any advise?

  "Error converting data type varchar to real...."

  ALter table [dbo].[Voice_PAYG] alter column [MO_BILLED_MINUTES] real;

  ALter table [dbo].[Voice_PAYG] alter column [MO_ACTUAL_MINUTES] dec(18,2);

  ALter table [dbo].[Voice_PAYG] alter column [CALLS] dec(18,2);

  ALter table [dbo].[Voice_PAYG] alter column [UNIQUE_CALLERS] dec(18,2);

  ALter table [dbo].[Voice_PAYG] alter column [MO_NET_REVENUE] dec(18,2);

  ALter table [dbo].[Voice_PAYG] alter column [MO_REVENUE] real;

  Is there a script that can convert columns of existing tables?

  Thanks!

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  March 25, 2014 at 5:55 am

  #1699813

  I'd suggest double checking the data in the column. It sounds like you may have text data mixed in with the numbers.

  ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

• djj

  SSCoach

  Points: 18831

  More actions

  March 25, 2014 at 6:41 am

  #1699836

  If there are several columns that need changed you may be better off creating a new table and inserting the data there.

  As Grant said you need to check your data, which could be done with the select statement used to insert the data.

  --INSERT INTO abc (X)

  SELECT CAST( X AS REAL) AS X

  If you do one column at a time you will find the problem.