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

Question

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

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes are usually used to object names (e.g. column_name as "Emp Name"). — DineshDB
– DineshDB, Commented Jun 24, 2016 at 7:36
Watch out for (and read up on) sql injection. Check out the quoteidentifier and similar functions. — Brimstedt
– Brimstedt, Commented Jun 24, 2016 at 9:09

TheGameiswar · Accepted Answer · 2016-06-24 07:16:04Z

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

answered Jun 24, 2016 at 7:16

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TKrish Over a year ago

That works. Can u explain me about that. Better ' ' ' ' than " " . Btw Thank you very much.

TheGameiswar Over a year ago

@TKrish: your are tyring to use strings like in c#.in sql you have set strings with single quote and there was a redundant ++.always print your SQL so that you can catch errors yourself

DineshDB · Accepted Answer · 2016-06-24 07:14:34Z

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

answered Jun 24, 2016 at 7:14

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

5 Comments

TKrish Over a year ago

That works. Can u explain me about that. Better ' ' ' ' than " " . Btw Thank you very much.

DineshDB Over a year ago

In SQL Server the Double Quotes are just consider as a String value, But Single Quotes are used to Hold the String values. That's the problem in your Code.

TheGameiswar Over a year ago

@DineshDB: In SQL Server the Double Quotes are just consider as a String value ..? this is not correct

DineshDB Over a year ago

@TheGameIsWar: Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes are usually used to object names (e.g. column_name as "Emp Name").

DineshDB Over a year ago

Hope my next comment is Correct.

Arulkumar · Accepted Answer · 2016-06-24 08:27:44Z

1

Instead of double quotes ", use two single quotes '' in these places " 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 ...'

edited Jun 24, 2016 at 8:27

answered Jun 24, 2016 at 7:19

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

1 Comment

TKrish Over a year ago

That works. Can u explain me about that. Better ' ' ' ' than " " . Btw Thank you very much.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related