Error converting data type varchar to numeric

parth Patel 1 Reputation point
2021-11-22T05:43:34.32+00:00

I have following in the select case tsql.
CASE WHEN CAST(ISNULL(c.MEMBER_TIERBAL,'0') AS numeric(18,2)) - CAST(ISNULL(p.PointsAvailable,'0') AS numeric(18,2)) < -10 THEN 'Band 1'

When I run the select statement, it fails with an error "Error converting data type varchar to numeric"

How can I convert it without any errors.

Thank you..

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-22T05:46:28.58+00:00

    Hi @parth Patel ,

    Welcome to the microsoft TSQL Q&A forum!

    Please try: 

        CASE WHEN   
    TRY_CAST(ISNULL(c.MEMBER_TIERBAL,'0')   
    AS numeric(18,2)) -   
    TRY_CAST(ISNULL(p.PointsAvailable,'0')   
    AS numeric(18,2)) < -10   
    THEN 'Band 1'

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards,
    Echo

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Ronen Ariely 15,096 Reputation points
    2021-11-22T06:58:56.85+00:00

    My 2 cents

    > How can I convert it without any errors.

    (1) Why?!? Errors are good thing! Errors are the best friend of the developers!

    Errors are not for fun but for a VERY good reason!

    Hiding error is the usually the way a low level architect/developer go around an issue which he cannot fix, instead of fixing the source error. Whenever I see the use of TRY_CAST in a query, a red light comes on and indicates that someone has failed to solve a problem and tried to hide it

    Note! I am using the word "usually" since there are rare times when we might need it but these are probably on the client side and we will always want indication on such issues behind the scenes!

    What you need to do is to find the source of the issue!

    > ISNULL(c.MEMBER_TIERBAL,'0')

    (2) if the column does not supposed to be null then why did you let null to be stored? If you can implement a restriction before the data inserted to the database, then you can save yourself a lot of headache.

    > CAST(ISNULL(c.MEMBER_TIERBAL,'0') AS numeric(18,2))

    (3) (my personal approach) Decimal and numeric are synonyms. I highly recommend to use DECIAL instead of NUMERIC in your code.

    (4) DO NOT STORE DATA IN THE WRONG DATA TYPE!

    This is the most important point here! Do not store decimal data as text!

    • Text usually cost more place than decimal. check example below
    • Working with the data (math, compare, sort...) cost less since there is no need to CONVERT
    • Avoid such issue related to wrong format of the data.

    Check this:

    declare @D decimal(18,2),@T VARCHAR(18)
SELECT @D = 1, @T = &#39;1&#39;
SELECT DATALENGTH(@D),DATALENGTH(@T) -- 5:1
SELECT @D = 1.01, @T = &#39;1.01&#39;
SELECT @D, DATALENGTH(@D),DATALENGTH(@T) -- 5:4
SELECT @D = 11.01, @T = &#39;11.01&#39;
SELECT @D, DATALENGTH(@D),DATALENGTH(@T) -- 5:5
SELECT @D = 111.01, @T = &#39;111.01&#39;
SELECT @D, DATALENGTH(@D),DATALENGTH(@T) -- 5:6
SELECT @D = 1234567891234567.01, @T = &#39;1234567891234567.01&#39;
SELECT @D, DATALENGTH(@D),DATALENGTH(@T) -- 9:18

    Golden rule: fix the data BEFORE you store it, as soon as possible in the app life circle.

    If you have a GUI with a form (for example a form in a web site) where users fill data which should be stored in the database, then

    • check the data before it even sent to the server in the client side (for example using JavaScript).
    • Next, In addition, check the data in the server side in your application before you send it to the database! Confirm that the data fits your expectation and if must fix the data according to your business logic rules.
    • Make sure to use the right type for the data! Do not store numbers as string for example.
    • If there are limitations regarding the data such as a certain format or size above some value (all the logical business rules that need to be implemented) then implement it at the database level - this is why we have constraint
    • configure a default value in the column level if such is needed and not when you use the data.

    Golden rule! Do not hide errors but solve errors.

    Golden rule: enforce the data to be stored in the right data type

    Golden rule: Do not give excuses that you got the system from someone else when you continue to use the system without fix it

    This is on you now! If you got a system which is not well design and you are in charge on it now then you should fix it step by step. This is a good example why ERROR ARE THE BEST FRIEND OF THE DEVELOPER! if you got an error then you know that you should fix it. If the error is hidden then you might have a mistake like an application which change the value that is stored since it "did not fit the code" as you will get if you are using TRY_CAST for example and enforce default value in the query level.

    0 comments