none
Conversion failed when converting the varchar value to data type int. RRS feed

  • Question

  • Dear All,

    When I am firing a below query  I am getting the error

    SELECT 
    Empcode,
    Date_of_Joining,
    LastWorkingDate,
    Tenure,
    CASE WHEN CAST( Tenure AS INTEGER) =1 THEN 'THREE' ELSE Tenure END TEN

    FROM #TEMP

    tenure I AM getting bu substracting lastworking date to date of joining

    Error:-

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'THREE' to data type int.

    Regards,

    Vipin jha


    Thankx & regards, Vipin jha MCP

    • Changed type Naomi NModerator Monday, January 7, 2013 4:41 PM Question rather than discussion
    Monday, January 7, 2013 6:10 AM

Answers

  • Try this,

    The problem is you are trying to mix int and varchar datatypes together.

    SELECT 
    Empcode,
    Date_of_Joining,
    LastWorkingDate,
    Tenure,
    CASE WHEN CAST( Tenure AS INTEGER) =1 THEN 'THREE' ELSE Convert(Varchar(100),Tenure) END TEN
    
    FROM #TEMP
    Regards
    satheesh
    Monday, January 7, 2013 7:21 AM
  • Try

    SELECT 
    Empcode,
    Date_of_Joining,
    LastWorkingDate,
    Tenure,
    CASE WHEN Tenure NOT LIKE '%[^0-9]%' then when CAST( Tenure AS INTEGER) =1 THEN 'THREE' 
    else cast(Tenure as varchar(20)) end
    ELSE cast(Tenure as varchar(20)) END As TEN


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, January 7, 2013 4:45 PM
    Moderator

All replies

  • Please make sure Tenure column is having only numeric inside.

    Many Thanks & Best Regards, Hua Min

    Monday, January 7, 2013 6:16 AM
  • Hi Vipin,

    You need to consider other values of "Tenure" column.

    For example, what if "CAST( Tenure AS INTEGER)" is not 1? 

    Then your CASE output will be an integer. What will be the data type for "Ten" column? VARCHAR or INT?

    If your CASE condition evaluates to true, the output is varchar ("THREE") otherwise it is INT (I mean original value of "Tenure" column).

    In this situation the "Ten" column contains both VARCHAR and INT!

    Monday, January 7, 2013 6:20 AM
  • The order of precedence between VARCHAR/INT is such that the lower precedence is converted to the higher precedence. In this case it is trying to convert the varchar value "THREE" (lower precedence) is converted to INT (higer precedence) resulting in error.

    As already said above by Amin, you cannot return different datatypes in CASE unless they can be implicity converted to the higher precedence datatype

    Change your query to below

    SELECT 
     Empcode,
     Date_of_Joining,
     LastWorkingDate,
     Tenure,
     CASE 
    	WHEN CAST(Tenure AS INTEGER) = 1 THEN 'THREE' 
    	ELSE CAST(Tenure AS VARCHAR) 
     END TEN
    FROM #TEMP

    Monday, January 7, 2013 7:00 AM
  • Try this,

    The problem is you are trying to mix int and varchar datatypes together.

    SELECT 
    Empcode,
    Date_of_Joining,
    LastWorkingDate,
    Tenure,
    CASE WHEN CAST( Tenure AS INTEGER) =1 THEN 'THREE' ELSE Convert(Varchar(100),Tenure) END TEN
    
    FROM #TEMP
    Regards
    satheesh
    Monday, January 7, 2013 7:21 AM
  • Try

    SELECT 
    Empcode,
    Date_of_Joining,
    LastWorkingDate,
    Tenure,
    CASE WHEN Tenure NOT LIKE '%[^0-9]%' then when CAST( Tenure AS INTEGER) =1 THEN 'THREE' 
    else cast(Tenure as varchar(20)) end
    ELSE cast(Tenure as varchar(20)) END As TEN


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, January 7, 2013 4:45 PM
    Moderator