Am getting an error when i run this code

_{Tuesday, September 11, 2018 10:23 PM}

SELECT SalesOrderID + '(' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
   CONVERT(NVARCHAR(30), OrderDate, 120) AS OrderDate
FROM SalesLT.SalesOrderHeader;

Error:

('22018', "[22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the varchar value '(' to data type int. (245) (SQLExecDirectW)")

_{Tuesday, September 11, 2018 11:30 PM}

SELECT SalesOrderID + '(' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
   CONVERT(NVARCHAR(30), OrderDate, 120) AS OrderDate
FROM SalesLT.SalesOrderHeader;

Error:

('22018', "[22018] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the varchar value '(' to data type int. (245) (SQLExecDirectW)")

Good day,

It could help if you provided the table structure so we will not need to guess, but in the meantime...

I assume that SalesOrderID is type INT and therefore you "SalesOrderID + '('" will raise the error since you try to add INT to STRING and the server cannot read your mind so it try to implicitly CONVERT the string '(' into INT so it could do the math +

You should always use explicit CONVERT like:

CONVERT(NVARCHAR(MAX), SalesOrderID) + '('

instead of:

SalesOrderID + '('

** Since I have no idea what i the real types in the table, I CONVERT the type if the INT to NVARCHAR(MAX) , but once we will have the real information we might want to convert it to a different type of STRING

*** For more information please ALWAYS provide 

1) Queries to CREATE your table(s) including indexes
2) Queries  to INSERT sample data.

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

_{Wednesday, September 12, 2018 2:01 AM}

Please post DDL as required by forum netiquette. You apparently don't understand the type structures in SQL. Since we have a date data type, we would never use the old Sybase CONVERT () function to turn it into a string. You had to do that in COBOL for display purposes, but that was 50 years ago.

Next, this error seems to be caused by the fact that an identifier can never be numeric. You don't do any calculations on identifiers by their very nature. They use what is called a nominal scale in data modeling. Just think about it; what does the square root of your credit card number mean? 

The final question is does the revision number have some special meaning? Since we have no specs, we don't know. I will make a guess that you might want to use the CREATE SEQUENCE  statement to add a revision sequence column to your table.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

_{Wednesday, September 12, 2018 7:29 AM}

This happens because when two types meet in SQL Server, the type with lower precedence will be converted to the type with higher precedence, if an implicit conversion exists, that is. And unfortunately there is implicit conversion between strings and numbers. And strings have lower precedence than numbers.

Therefore, you must use an explicit convert of the numbers to make them all strings as Ronen showed you. There is also a second option if you are on SQL 2012 or later. You can use the concat function which converts all its input to strings:

    concat(SalesOrderID, '(', RevisionNumber, + ')')

Also, you should probably not format the date in the SELECT query, but this should client-side, so that the user's regional settings can be respected.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Wednesday, September 12, 2018 8:04 AM}

I think SalesOrderID is of numerical data type here (int,bigint,etc) which has higher precedence over string and causes entire value to be converted to string which will break for values like (

I think what you can do is either of the below

SELECT CAST(SalesOrderID AS varchar(20)) + '(' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
   CONVERT(NVARCHAR(30), OrderDate, 120) AS OrderDate
FROM SalesLT.SalesOrderHeader;

Or

SELECT CONCAT(SalesOrderID ,'(',STR(RevisionNumber, 1),')') AS OrderRevision,
   CONVERT(NVARCHAR(30), OrderDate, 120) AS OrderDate
FROM SalesLT.SalesOrderHeader;

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page