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: 398911

  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 Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• 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.