Conversion failed when converting the varchar value of string to data type int-CodePudding

I got issue with my SQL as below statement. After execute it, got error :

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

SELECT description, moreThanDay, 
CONCAT(moreThanDay 1,' - ' , 
  IIF(LEAD(moreThanDay) OVER (ORDER BY moreThanDay) > 0, 
    LEAD(moreThanDay) OVER (ORDER BY moreThanDay),'ONWARDS')) AS rangeDay 
FROM EquipmentOwnerDetention WHERE equipmentOwnerCode=@equipmentOwnerCode

Is anyone know on how to cast so that the result will turn out as string ? Thanks in advances.

CodePudding user response：

The error is happening because moreThanDay appears to be numeric, and you can't directly concatenate a numeric value to a string. For brevity, I might include a CTE here:

WITH cte AS (
    SELECT *, LEAD(moreThanDay) OVER (ORDER BY moreThanDay) moreThanDayLead
    FROM EquipmentOwnerDetention
    WHERE equipmentOwnerCode = @equipmentOwnerCode
)

SELECT description, moreThanDay,
       CONCAT(CAST(moreThanDay, VARCHAR(max)), ' - ',
              IIF(moreThanDayLead > 0, CAST(moreThanDayLead, VARCHAR(max)), 'ONWARDS')) AS rangeDay
FROM cte;

CodePudding user response：

The second parameter in IIF is int and 'ONWARDS' is string. IIF expects both to be of same data type. Try converting the first parameter to string and it may work.

Something like this


SELECT description, moreThanDay, 
CONCAT(moreThanDay 1,' - ' , 
  IIF(LEAD(moreThanDay) OVER (ORDER BY moreThanDay) > 0, 
    STR(LEAD(moreThanDay) OVER (ORDER BY moreThanDay), 10),'ONWARDS')) AS rangeDay 
FROM EquipmentOwnerDetention WHERE equipmentOwnerCode=@equipmentOwnerCode