TKrish TKrish - 5 months ago 9
SQL Question

Concat String to Int in sql server.- But getting error

When I'm trying to retrieve data by this, It works.

SELECT
CONVERT(VARCHAR(10),BH.Doc_Date,(120))AS Doc_Date
,LGH.Node_Code
,LGH.Node_Name AS Location_Name
,GD.Nationality
,GD.Guest_Name
,DBOGD.Name AS Gender
,CAST(DATEDIFF(yy,GD.Birth_Day,getdate()) AS NVARCHAR(100)) + ' yrs' AS Age
,'++' + CAST(GD.Mobile AS VARCHAR(100)) AS Mobile
,GD.NIC_No
,GD.Language
FROM [booking].[Guest_Details] AS GD ...


But when I'm trying to do like this,

SET @query = 'SELECT
CONVERT(VARCHAR(10)
,BH.Doc_Date,(120))AS Doc_Date
,LGH.Node_Code
,LGH.Node_Name AS Location_Name
,GD.Nationality
,GD.Guest_Name
,DBOGD.Name AS Gender
,(CAST(DATEDIFF(yy,GD.Birth_Day,getdate()) AS NVARCHAR(100)) + " yrs") AS Age
,"++" + CAST(GD.Mobile AS VARCHAR(100)) AS Mobile
,GD.NIC_No
,GD.Language
FROM [booking].[Guest_Details] AS GD ...'


It's not working. I'm getting Errors like:


Invalid column name ' yrs'.

Invalid column name '++'.

Answer

Try this code, hope this helps you.

SET @query = 'SELECT 
                CONVERT(VARCHAR(10)
                ,BH.Doc_Date,(120))AS Doc_Date
                ,LGH.Node_Code
                ,LGH.Node_Name AS Location_Name
                ,GD.Nationality
                ,GD.Guest_Name
                ,DBOGD.Name AS Gender
                ,(CAST(DATEDIFF(yy,GD.Birth_Day,getdate()) AS NVARCHAR(100)) + '' yrs'') AS Age
                ,''++'' + CAST(GD.Mobile AS VARCHAR(100)) AS Mobile
                ,GD.NIC_No
                ,GD.Language
                FROM [booking].[Guest_Details] AS GD ...'
Comments