在sql服务器中将字符串转换为Int。——但让错误

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'.

无效列名“yrs”。

Invalid column name '++'.

无效列名“+ +”。

3 个解决方案

#1

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 ...'

#2

Yrs part is causing you issue..Modify it like below

你的问题出在你身上。修改它像下面

   ,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 ...'

#3

Instead of double quotes ", use two single quotes '' in these places " yrs", "++".

用两个单引号代替双引号，在这些地方用“yrs”，“++ +”。

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 -- in this line
    ,''++'' + CAST(GD.Mobile AS VARCHAR(100)) AS Mobile -- and in this line
    ,GD.NIC_No
    ,GD.Language
    FROM [booking].[Guest_Details] AS GD ...'

#1