Click here to Skip to main content
14,268,557 members
Rate this:
Please Sign up or sign in to vote.
See more:
Testing out Converting Pipelen but getting a conversion failed.
Conversion failed when converting the nvarchar value 'N/A' to data type int


What I have tried:

SELECT        MapNo, FromAssetID, ToAssetid, CompKey, Location, InstallationDate, PipeDia, PipeType,PipeLen, ConstructionMethods, District, JobNo, ProjectID, ProjectName, ProposalId, ProposalName, UnitType, 
                         PipeDepth, ImplementationPhase, UrgentFix, DesignStatus, ConstructionStatus, ImplementedProjectAutoID, Assetid, AssetStatus, AssetSummary,
						 Convert(NVARCHAR(20),PipeLen /12) + '''' + Convert(NVARCHAR(20),PipeLen %12)+'"' as PipeMiles
						 
FROM            VW_CPMS_Report_AssetsAndTheirAssoiciatedProjectsOrProposals
Posted
Updated 2-Aug-19 11:07am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Conversion failed when converting the nvarchar value 'N/A' to data type int
Well, this is pretty self explanatory; SQL was expecting an INTeger value and instead it got an NVARCHAR.

My guess would be that your source table has the PipeLen field defined as an NVARCHAR and that one of the rows is populated with "N/A"

If this indeed the case... the way to fix it is to check to see if that field can be Parsed as an INT before you try to do math with it.
In SQL Server, this would be done using the Try_Parse function; which will return a NULL if it cannot be parsed.

I greatly abbreviated what I think you have for a structure into just the basics, and put this proof of concept together. You naturally will need to tailor it into your own SQL on your own.
DECLARE @Temp TABLE (RowID INT IDENTITY (1,1), PipeLen NVARCHAR(20))

INSERT @temp
VALUES ('12'),	('18'), ('24'), ('33'), ('n/a')

SELECT  *
     ,  PipeMiles = CASE WHEN TRY_PARSE(PipeLen as INT) IS NULL
                         THEN  'n/a'
                         ELSE 	Convert(NVARCHAR(20), PipeLen /12)
		 						+ ''''
								+ Convert(NVARCHAR(20) ,PipeLen %12)+'"' 	
					END
FROM  @Temp
which returns this recordset
RowID  PipeLen  PipeMiles
-----  -------  ---------
1      12       1'0"
2      18       1'6"
3      24       2'0"
4      33       2'9"
5      n/a      n/a

References:
1. TRY_PARSE (Transact-SQL) - SQL Server | Microsoft Docs[^]
2. SQL Server TRY_PARSE() Function Explained By Examples[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100