sqlcastingtype-conversionintegervarchar

Conversion failed when converting the varchar value '1.1' to data type int


I have two columns, one of data type Smallint and the other int. I am trying to concatenate the two columns and a '.' to produce one final value but I keep getting the error:

Conversion failed when converting the varchar value '1.1' to data type int

For instance, if I have '1' in ColumnA and a '1' in ColumnB, I want to concatenate them like CONCAT(ColumnA, '.', ColumnB) to give me the value '1.1'.

This is my code:

CAST(CASE 
         WHEN ColumnA IS NOT NULL AND (ColumnB = 0 OR ColumnB IS NULL) 
             THEN ColumnA
         WHEN ColumnA IS NOT NULL AND ColumnB IS NOT NULL AND ColumnB > 0 
             THEN CONCAT(ColumnA, '.', ColumnB) 
     END AS NUMERIC(2, 1)) AS 'VERSION'

I have tried all different variations of a CAST, but nothing seems to be working. Any help would be much appreciated. Many thanks in advance.


Solution

  • ColumnA is a SMALLINT in the first case so all following cases must also be convertable to INT. If we Cast the first case into a VARCHAR then all following cases can be VARCHAR. CONCAT produces a VARCHAR.

     SELECT *, CAST(
                    CASE WHEN ColumnA IS NOT NULL AND (ColumnB = 0 OR ColumnB IS NULL) THEN CAST(ColumnA as Varchar(10))
                         WHEN ColumnA IS NOT NULL AND ColumnB IS NOT NULL AND ColumnB > 0 THEN CONCAT(ColumnA, '.', ColumnB) 
                    END 
                    AS NUMERIC(2,1)
                   ) AS 'VERSION'
    FROM Example
    

    fiddle

    ColumnA ColumnB VERSION
    1 1 1.1
    2 2 2.2
    3 4 3.4