Error converting data type varchar to numeric

  • Hi

    My Table is:

    CREATE TABLE CDN

    (

    [# CP Code] varchar(50),

    [Time] varchar(50),

    [Total Volume in MB] varchar(50)

    )

    INSERT INTO CDN

    SELECT '903','7/18/2010','224195.504' UNION ALL

    SELECT '903','7/19/2010','222747.2187' UNION ALL

    SELECT '903','7/20/2010','231171.2056' UNION ALL

    SELECT '903','7/21/2010','217138.7239' UNION ALL

    SELECT '903','7/22/2010','220184.8136' UNION ALL

    SELECT '903','7/23/2010','213464.8818' UNION ALL

    SELECT '903','7/24/2010','225835.5601' UNION ALL

    SELECT '1204','7/18/2010','357220.6254' UNION ALL

    SELECT '1204','7/19/2010','369738.3753' UNION ALL

    SELECT '1204','7/20/2010','350584.1343' UNION ALL

    SELECT '1204','7/21/2010','365990.1172' UNION ALL

    SELECT '1204','7/22/2010','366660.9649' UNION ALL

    SELECT '1204','7/23/2010','348032.4622' UNION ALL

    SELECT '1204','7/24/2010','364754.5849' UNION ALL

    SELECT '2015','7/18/2010','131121.4758' UNION ALL

    SELECT '2015','7/19/2010','131896.0879' UNION ALL

    SELECT '2015','7/20/2010','142174.8376' UNION ALL

    SELECT '2015','7/21/2010','139842.816' UNION ALL

    SELECT '2015','7/22/2010','132125.7374' UNION ALL

    SELECT '2015','7/23/2010','138561.1871' UNION ALL

    SELECT '2015','7/24/2010','132163.1733' UNION ALL

    SELECT '17190','7/18/2010','41.9916' UNION ALL

    SELECT '17190','7/19/2010','42.0314' UNION ALL

    SELECT '17190','7/20/2010','63.9657' UNION ALL

    SELECT '17190','7/21/2010','54.0577' UNION ALL

    SELECT '17190','7/22/2010','10.7518' UNION ALL

    SELECT '17190','7/23/2010','95.443' UNION ALL

    SELECT '17190','7/24/2010','165.1344' UNION ALL

    SELECT '17192','7/18/2010','5.44E+07' UNION ALL

    SELECT '17192','7/20/2010','5.70E+07' UNION ALL

    SELECT '17192','7/21/2010','5.88E+07' UNION ALL

    SELECT '17192','7/22/2010','5.92E+07' UNION ALL

    SELECT '17192','7/23/2010','8.29E+07' UNION ALL

    SELECT '17192','7/24/2010','6.68E+07'

    I have Created a View on this Table as:

    CREATE VIEW View_CDN

    AS

    SELECT

    CAST([# CP Code] AS CHAR(5)) AS [CP Code],

    CAST([Time] AS DATETIME) AS [Date],

    CAST([Total Volume in MB] AS Decimal(15,8)) AS [TotalVolumeinMB]

    FROM CDN

    GO

    But when I try to run:

    SELECT * FROM View_CDN

    GO

    It gives me the below error:

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to numeric.

    I know the problem is with the [Total Volume in MB] Column. But how to correct this problem?

    Thanks

  • Hi

    Yes,working with CSV files is really hard. Lot of manual formating has to be done.

    SELECT CAST ('6.68E+07' AS FLOAT)

    Do I have to do this for every such value? I have a lot of these.

    How would this be included into my above query?

    Thanks

  • you don't need to do that for all manually, use an update statement.. to update all values with 'E' in it using a case statement with Patindex

  • Hi

    Divyanth,Can you update my query with what you are saying ?

    Thanks

  • I would suggest using a SELECT that CELKO suggests as updating is always expensive..But if you still want to below is the code

    UPDATE TableName

    SET ColName = CAST (CAST(ColName AS FLOAT) AS DECIMAL(15,8))

    WHERE PATINDEX('%E%',ColName) <> 0

  • Hi,

    Thankyou guys, great help.

    @celko .... I can't BULK INSERT from a CSV file into the table if I use the right datatypes. So I have to do all the manipulations in Views. I can only import data if I use VARCHAR. Also for now I don't want to use any ETL tools.

    @divyanth .... Thanks for the query .... but CELKO's query works for me as I don't want to change the table coz if I do, I would not be able to insert more data into the table from other CSV files.

    Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply