CODEWITHSUNDEEP
sql
user1285520
user1285520

Reputation: 11

I can't get this error fixed : conversion failed when converting the nvarchar value

I am a newby at sql and can find the problem with my query, I am getting this error: Msg 245, Level 16, State 1, Line 10 Conversion failed when converting the nvarchar value '00999-00-210312-11' to data type int.

Declare @Referenceid Int

SET @Referenceid = (
                    SELECT TOP 1 (CASE WHEN ad.ReferenceID LIKE '%NO ID%' THEN (RIGHT(MAX(ad.ReferenceID),2)+10)END) 
                    FROM Campaign.dbo.Assets_DailyBulkImport AS ad
                    WHERE (ad.ReferenceID IS NOT NULL)
                    GROUP BY ad.ReferenceID
                    ORDER BY ad.ReferenceID DESC);

Select (c.Note + '' + '999' + N'-' + '00' + N'-' +
                      (SELECT     REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3), '/', '') AS MMDDYY) + N'-' + @Referenceid)
                  FROM  Assets_DailyBulkImport AS a INNER JOIN
                        Controls_Profiles AS c ON a.Profile = c.Venture
                  where a.ReferenceID=@ReferenceID

Upvotes: 1

Views: 802

Answers (1)

Pranay Rana
Pranay Rana

Reputation: 176956

it says that '00999-00-210312-11' is a string value you cannot convert that in integer value. as you can see - is part of data so it must be string i.e varchar its not integer value that the reason its giving error.

so to resolve you error you need to change this line 

 (SELECT     REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3), '/', '') AS MMDDYY) + N'-' 
+ cast(@Referenceid as varchar(10))

in your code @Referenceid is integer value you cannot directly append it you first need to convert it like cast(@Referenceid as varchar(10))

Upvotes: 1

Related Questions