Ошибка при преобразовании типа данных nvarchar к numeric

You can’t easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland’s complaint about this on UserVoice). There are cases where you can nest CTEs until the cows come home, but SQL Server will still push or pull that evaluation to a place where you didn’t expect it.

As long as aggregates and fulltext functions aren’t involved (see here and here), you can force this evaluation before conversion by using a CASE expression. Here is a simplified version of your query with no CTEs:

SELECT cfv.issue,
  priority_num = CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)
FROM
  proddb1.customfieldvalue AS cfv
  INNER JOIN proddb1.customfield AS cf
  ON cfv.CUSTOMFIELD = cf.id
  INNER JOIN proddb1.customfieldoption AS cfo
  ON cfv.CUSTOMFIELD = cfo.CUSTOMFIELD
  AND CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END) = cfo.id
  WHERE cf.cfname = 'Issue Priority';

You will need to change something, something to the proper precision/scale (or use one of the int types if you don’t need decimal places). You should never declare varying types without specifying the length — this blog post is about varchar, but it really applies to all of the types.

If you are on SQL Server 2012 (it’s always useful to include information like what version of SQL Server you’re using), you can simplify this:

CONVERT(NUMERIC(something, something), 
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)

to this:

TRY_CONVERT(NUMERIC(something, something), cfo.customvalue)

And it will actually be more reliable (since ISNUMERIC can return 1 and still fail at conversion time for specific types). I blogged about this way back in 2002.

While developing data processes in SQL Server, under certain circumstances, you might get the error message: error converting varchar to numeric. This error is similar with the conversion error you might get when you are trying to convert a varchar to float, etc.

Read on to find out the reason for getting this error message and how you can easily resolve it within just a minute.

The Numeric Data Type in SQL Server

Prior to discuss how you can reproduce and resolve the issue, it is important that you first understand the numeric data type in SQL Server. As described in the relevant MS Docs article, the numeric data type has fixed precision and scale, and it has equivalent functionality with the decimal data type.

Arguments

The numeric data type takes two arguments, that is precision and scale. The syntax is numeric(precision, scale).

Precision defines the maximum number of decimal digits (in both sides of the number) and its value range is between 1 and 38.

Scale, defines the number of decimal digit that will be stored to the right of the decimal point. Its value can range between 1 and the value specified for precision.

Here’s an example of a numeric data type value in SQL Server:

DECLARE @numValue NUMERIC(10,2);
SET @numValue=123456.7890

SELECT @numValue as NumValue;
GO

The number returned by the above T-SQL query is: 123456.7890

In the above example I specified as precision 10 and as scale 2.

So, even though I specified 123456.7890 as the numeric value, it was indirectly converted to a numeric(10,2) value and that’s why it returned the value 123456.79

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $14.99

Reproducing the Conversion Error

Great. Now, let’s reproduce the conversion error by trying to convert a “problematic” varchar value to numeric.

You can find this example below:

DECLARE @valueToConvert VARCHAR(50);
SET @valueToConvert='1123,456.7890';

SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber;
GO

When you execute the above T-SQL code, you will get the below exact error message:

Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

How to Resolve the Conversion Error

As you might have observed in the above example, the @valueToConvert variable, besides the dot (.), it also contains a comma (,).

Therefore, at the time of its conversion to the numeric data type, the comma is considered an illegal character for the destination data type (numeric) and that’s why you get the error message.

In order to resolve the conversion error, you just need to remove the comma (,) from the varchar value that you want to convert to numeric.

Note: At this point, you also need to make sure that the varchar value to be converted, is the actual number you wish to convert to the numeric data type. Also, you need to make sure that you only use the decimal symbol, in this case the dot (.), and not any digit grouping symbols, etc.

So, if we remove the comma from the above example, we can see that the conversion is successful.

DECLARE @valueToConvert VARCHAR(50);
SET @valueToConvert='1123456.7890';

SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber;
GO

Output:

In general, when converting varchar values to numbers (i.e. decimal, numeric, etc.), you need to be careful in order for your varchar value, not contain any digit grouping symbols (i.e. a comma) or any other characters that do not have a meaning as a number.

Check our Online Courses

• SQL Server 2022: What’s New – New and Enhanced Features [New]
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

Read Also

Feel free to check our other relevant articles on SQL Server troubleshooting:

• Error converting data type varchar to float
• SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
• SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
• ORDER BY items must appear in the select list if SELECT DISTINCT is specified
• There is no SQL Server Failover Cluster Available to Join
• There is insufficient system memory in resource pool ‘internal’ to run this query.
• There is not enough space on the disk. (mscorlib)
• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• Introduction to Azure Database for MySQL (Course Preview)
• [Resolved] Operand type clash: int is incompatible with uniqueidentifier
• The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it
• SQL Server replication requires the actual server name to make a connection to the server – How to Resolve it
• Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
• Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
• SQL Server is Terminating Because of Fatal Exception 80000003 – How to Troubleshoot
• An existing History Table cannot be specified with LEDGER=ON – How to Resolve
• … all SQL Server troubleshooting articles

Featured Database Productivity Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Learn more

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn more

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

Rate this article: (3 votes, average: 4.33 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

How to resolve the error: Error converting varchar to numeric in SQL Server

Click to Tweet

Views: 18,939

i m making a registration form… where i want the should go in data table according to choice selected by user…
but when i click the «submit» button. it shows me an error like this…

Server Error in '/project@water_billing_system' Application.

Error converting data type nvarchar to numeric.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error: 


Line 521:            cmd.Parameters.Add(prm_ac_code_password);
Line 522:
Line 523:            cmd.ExecuteNonQuery();
Line 524:            conn.Close();
Line 525:        }

Source File: f:water billing systemproject@water_billing_systemonline_redistration.aspx.cs    Line: 523 

i m using stored procedure for this…
and the store procedure is like below.

Quote:

@registration_datetime datetime,
@ac_code_id varchar(8),
@ac_password varchar(max),
@owner_surname varchar(30),
@owner_first_name varchar(30),
@owner_middle_name varchar(30),
@owner_DOB date,
@add_of_c_industry varchar(MAX),
@handler1_name varchar(50),
@handler1_mobile numeric(14,0),
@handler1_landline numeric(18,0),
@handler1_email varchar(50),
@handler2_name varchar(50),
@handler2_mobile numeric(14,0),
@handler2_landline numeric(18,0),
@handler2_email varchar(50),
@handler3_name varchar(50),
@handler3_mobile numeric(14,0),
@handler3_landline numeric(18,0),
@handler3_email varchar(50),
@connection_type_id int,
@connection_size_id int,
@connection_plan_id int,
@connection_start_wish date

all the data type is same in sqltable as above…

and this is my «aspx.cs» code…

protected void btn_submit_Click(object sender, EventArgs e)
    {
        string owner_birthdate,wish_start_from,ac_code_id;
        owner_birthdate = ddl_owner_dob_mm.Text + '/' + ddl_owner_dob_dd.Text + '/' + ddl_owner_dob_yyyy.Text;
        wish_start_from=ddl_conn_wish_mm.Text+'/'+ddl_conn_wish_dd.Text+'/'+ddl_conn_wish_yyyy.Text;

        if (rbl_conn_for.SelectedValue == "1")
        {
            string hp="HP";

            conn.Open();
            SqlCommand cmd = new SqlCommand("insert_into_reg_housing_plot",conn);
            cmd.CommandType = CommandType.StoredProcedure;


            SqlParameter prm_datetime = new SqlParameter(lbl_date_time.Text, "@registration_datetime");
            cmd.Parameters.Add(prm_datetime);

            SqlParameter prm_owner_surname = new SqlParameter("@owner_surname", txt_owner_surname.Text);
            cmd.Parameters.Add(prm_owner_surname);

            SqlParameter prm_owner_first_name = new SqlParameter("@owner_first_name", txt_owner_first_name.Text);
            cmd.Parameters.Add(prm_owner_first_name);

            SqlParameter prm_owner_middle_name = new SqlParameter("@owner_middle_name", txt_owner_middle_name.Text);
            cmd.Parameters.Add(prm_owner_middle_name);

            SqlParameter prm_owner_DOB = new SqlParameter("@owner_DOB", owner_birthdate);
            cmd.Parameters.Add(prm_owner_DOB);

            SqlParameter prm_add_housing_plot = new SqlParameter("@add_of_housing_plot", txt_address_housing_plot.Text);
            cmd.Parameters.Add(prm_add_housing_plot);

            SqlParameter prm_handler1_name = new SqlParameter("@handler1_name", txt_handler1_name.Text);
            cmd.Parameters.Add(prm_handler1_name);

            SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
            cmd.Parameters.Add(prm_handler1_mobile);

            SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
            cmd.Parameters.Add(prm_handler1_landline);

            SqlParameter prm_handler1_email = new SqlParameter("@handler1_email", txt_handler1_email.Text);
            cmd.Parameters.Add(prm_handler1_email);

            SqlParameter prm_handler2_name = new SqlParameter("@handler2_name", txt_handler2_name.Text);
            cmd.Parameters.Add(prm_handler2_name);

            SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
            cmd.Parameters.Add(prm_handler2_mobile);

            SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
            cmd.Parameters.Add(prm_handler2_landline);

            SqlParameter prm_handler2_email = new SqlParameter("@handler2_email", txt_handler2_email.Text);
            cmd.Parameters.Add(prm_handler2_email);

            SqlParameter prm_handler3_name = new SqlParameter("@handler3_name", txt_handler3_name.Text);
            cmd.Parameters.Add(prm_handler3_name);

            SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
            cmd.Parameters.Add(prm_handler3_mobile);

            SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
            cmd.Parameters.Add(prm_handler3_landline);

            SqlParameter prm_handler3_email = new SqlParameter("@handler3_email", txt_handler3_email.Text);
            cmd.Parameters.Add(prm_handler3_email);

            if (rbl_connection_type.SelectedValue == "3")
            {
                SqlParameter prm_yearly_conn_type = new SqlParameter("@connection_type_id",rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_yearly_conn_type);

                SqlParameter prm_fix_yearly_size = new SqlParameter("@connection_size_id",ddl_conn_size_selection_for_fixed_yearly_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_size);

                SqlParameter prm_fix_yearly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_yearly_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "1")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_plant.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_size);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "2")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_conn_size_for_meter_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }

            SqlParameter prm_start_wish = new SqlParameter("@connection_start_wish",wish_start_from);
            cmd.Parameters.Add(prm_start_wish);


            SqlCommand ac = new SqlCommand("select max(ac_code_int) from registration_for_housing_plot",conn);
            object a;
            a=ac.ExecuteScalar();
            if (a.ToString() == "")
        {
            a="1001";
             ac_code_id= hp + a;
        }
        else
        {
            Int64 v;
            v = (Int64.Parse(a.ToString())) + 1;
            ac_code_id = hp + v.ToString();
            
           
        }
        
        SqlParameter prm_ac_code_id = new SqlParameter("@ac_code_id", ac_code_id);
        cmd.Parameters.Add(prm_ac_code_id);

        ac.ExecuteNonQuery();

        SqlParameter prm_ac_code_password = new SqlParameter("@ac_password",txt_confirm_password.Text);
        cmd.Parameters.Add(prm_ac_code_password);

        cmd.ExecuteNonQuery();
            conn.Close();
        }
        else if (rbl_conn_for.SelectedValue == "2")
        {
            string ci = "CI";

            conn.Open();
            SqlCommand cmd = new SqlCommand("insert_into_reg_c_industry", conn);
            cmd.CommandType = CommandType.StoredProcedure;


            SqlParameter prm_datetime = new SqlParameter("@registration_datetime", lbl_date_time.Text);
            cmd.Parameters.Add(prm_datetime);

            SqlParameter prm_owner_surname = new SqlParameter("@owner_surname", txt_owner_surname.Text);
            cmd.Parameters.Add(prm_owner_surname);

            SqlParameter prm_owner_first_name = new SqlParameter("@owner_first_name", txt_owner_first_name.Text);
            cmd.Parameters.Add(prm_owner_first_name);

            SqlParameter prm_owner_middle_name = new SqlParameter("@owner_middle_name", txt_owner_middle_name.Text);
            cmd.Parameters.Add(prm_owner_middle_name);

            SqlParameter prm_owner_DOB = new SqlParameter("@owner_DOB", owner_birthdate);
            cmd.Parameters.Add(prm_owner_DOB);

            SqlParameter prm_add_c_industry = new SqlParameter("@add_of_c_industry", txt_address_commercial_industry.Text);
            cmd.Parameters.Add(prm_add_c_industry);

            SqlParameter prm_handler1_name = new SqlParameter("@handler1_name", txt_handler1_name.Text);
            cmd.Parameters.Add(prm_handler1_name);

            SqlParameter prm_handler1_mobile = new SqlParameter("@handler1_mobile", txt_handler1_mobile.Text);
            cmd.Parameters.Add(prm_handler1_mobile);

            SqlParameter prm_handler1_landline = new SqlParameter("@handler1_landline", txt_handler1_landline.Text);
            cmd.Parameters.Add(prm_handler1_landline);

            SqlParameter prm_handler1_email = new SqlParameter("@handler1_email", txt_handler1_email.Text);
            cmd.Parameters.Add(prm_handler1_email);

            SqlParameter prm_handler2_name = new SqlParameter("@handler2_name", txt_handler2_name.Text);
            cmd.Parameters.Add(prm_handler2_name);

            SqlParameter prm_handler2_mobile = new SqlParameter("@handler2_mobile", txt_handler2_mobile.Text);
            cmd.Parameters.Add(prm_handler2_mobile);

            SqlParameter prm_handler2_landline = new SqlParameter("@handler2_landline", txt_handler2_landline.Text);
            cmd.Parameters.Add(prm_handler2_landline);

            SqlParameter prm_handler2_email = new SqlParameter("@handler2_email", txt_handler2_email.Text);
            cmd.Parameters.Add(prm_handler2_email);

            SqlParameter prm_handler3_name = new SqlParameter("@handler3_name", txt_handler3_name.Text);
            cmd.Parameters.Add(prm_handler3_name);

            SqlParameter prm_handler3_mobile = new SqlParameter("@handler3_mobile", txt_handler3_mobile.Text);
            cmd.Parameters.Add(prm_handler3_mobile);

            SqlParameter prm_handler3_landline = new SqlParameter("@handler3_landline", txt_handler3_landline.Text);
            cmd.Parameters.Add(prm_handler3_landline);

            SqlParameter prm_handler3_email = new SqlParameter("@handler3_email", txt_handler3_email.Text);
            cmd.Parameters.Add(prm_handler3_email);

            if (rbl_connection_type.SelectedValue == "3")
            {
                SqlParameter prm_yearly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_yearly_conn_type);

                SqlParameter prm_fix_yearly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_yearly_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_size);

                SqlParameter prm_fix_yearly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_yearly_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_yearly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "1")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_size = new SqlParameter("@connection_size_id", ddl_conn_size_selection_for_fixed_plant.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_size);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_plan_selection_for_fixed_conn.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }
            else if (rbl_connection_type.SelectedValue == "2")
            {
                SqlParameter prm_monthly_conn_type = new SqlParameter("@connection_type_id", rbl_connection_type.SelectedValue);
                cmd.Parameters.Add(prm_monthly_conn_type);

                SqlParameter prm_fix_monthly_plan = new SqlParameter("@connection_plan_id", ddl_conn_size_for_meter_plan.SelectedValue);
                cmd.Parameters.Add(prm_fix_monthly_plan);
            }

            SqlParameter prm_start_wish = new SqlParameter("@connection_start_wish", wish_start_from);
            cmd.Parameters.Add(prm_start_wish);


            SqlCommand ac = new SqlCommand("select max(ac_code_int) from registration_for_comercial_industry",conn);
            object a;
            a = ac.ExecuteScalar();
            if (a.ToString() == "")
            {
                a = "1001";
                ac_code_id = ci + a;
            }
            else
            {
                Int64 v;
                v = (Int64.Parse(a.ToString())) + 1;
                ac_code_id = ci + v.ToString();


            }
            
            SqlParameter prm_ac_code_id = new SqlParameter("@ac_code_id", ac_code_id);
            cmd.Parameters.Add(prm_ac_code_id);

            ac.ExecuteNonQuery();

            SqlParameter prm_ac_code_password = new SqlParameter("@ac_password", txt_confirm_password.Text);
            cmd.Parameters.Add(prm_ac_code_password);

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

plzz help me solve this…
thanks a lot in advance…