Gowtham Ramamoorthy Gowtham Ramamoorthy - 2 months ago 13
SQL Question

concatenation using '+' symbol gives me error "Conversion failed when converting the varchar value 'current quarter' to data type int."

i'm trying to concat few strings using + symbol... when i use concat function it is working fine but when i use '+' symbol to concat the strings it throws an error.

Anyone know why this issue happening ?
below is the code using concat which works fine

Begin
DECLARE @VAR1 AS VARCHAR(60)
SET @VAR1 = concat ((SELECT MIN(TimeDimension_Year) FROM TimeDimension_LU WHERE TimeDimension_TimeDimensionType_ID = 15), '-','current quarter')
SELECT @VAR1
end


below is the same code using '+' symbol but gets failed

Begin
DECLARE @VAR1 AS VARCHAR(60)
SET @VAR1 = (SELECT MIN(TimeDimension_Year) FROM TimeDimension_LU WHERE TimeDimension_TimeDimensionType_ID = 15)+ '-'+'current quarter'
SELECT @VAR1
end

Answer

Obviously something is not a string. That would be the TimeDimension_Year column. So:

SET @VAR1 = (SELECT CAST(MIN(TimeDimension_Year) as varchar(255))
             FROM TimeDimension_LU
             WHERE TimeDimension_TimeDimensionType_ID = 15
            ) + '-' + 'current quarter';

This is a bit verbose for my tastes. I would just go with:

SELECT @VAR1 = CAST(MIN(TimeDimension_Year) as varchar(255)) + '-' + 'current quarter'
FROM TimeDimension_LU
WHERE TimeDimension_TimeDimensionType_ID = 15;