Convert error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert error

Author

Topic

phong919
Starting Member

29 Posts

Posted - 2008-03-28 : 13:02:35

Hello i get the following error when executing the query with the following column:

error - Conversion failed when converting the nvarchar value '5' to data type int. i'm not sure where the error is occuring. any assistance will be greatly appreciated.

SELECT dbo.tblSS_Milestone.iProjID, ProjectServer.dbo.GICAP_Engagement_IDs.ENG_ID AS Engagement_ID, CASE WHEN isnumeric(substring(sTask, 2, 1))
= 1 AND isnumeric(substring(sTask, 3, 1)) = 1 THEN CONVERT(integer, substring(sTask, 2, 2)) WHEN isnumeric(substring(sTask, 2, 1))
= 1 THEN CONVERT(integer, substring(sTask, 2, 1)) END AS [Milestone Level], CASE WHEN isnumeric(substring(sTask, 2, 1)) = 1 AND
isnumeric(substring(sTask, 3, 1)) = 1 THEN substring(sTask, 1, 3) WHEN isnumeric(substring(sTask, 2, 1)) = 1 THEN substring(sTask, 1, 2)
END AS Milestone, dbo.tblSS_Milestone.sTask AS [Milestone Desc], CONVERT(varchar, dbo.tblSS_Milestone.dtBaselineStart, 101) AS [Start Date],
CONVERT(varchar, dbo.tblSS_Milestone.dtBaselineFinish, 101) AS [Planned End Date], CONVERT(varchar, dbo.tblSS_Milestone.dtFinish, 101)
AS [Revised End Date], CONVERT(varchar, dbo.tblSS_Milestone.dtActualFinish, 101) AS [Actual Finish Date],
CASE WHEN iPercentcomplete = 0 THEN 'Not Started' WHEN iPercentcomplete > 0 AND
iPercentcomplete < 100 THEN 'In Progress' WHEN iPercentcomplete = 100 THEN 'Completed' END AS Status, dbo.tblSS_Milestone.iTaskUID
FROM ProgramServerUser.GICAP_SnapShot_ID_View INNER JOIN
dbo.tblSS_Milestone INNER JOIN
ProjectServer.dbo.GICAP_Engagement_IDs ON dbo.tblSS_Milestone.iProjID = ProjectServer.dbo.GICAP_Engagement_IDs.PROJ_ID ON
ProgramServerUser.GICAP_SnapShot_ID_View.idSnapShot = dbo.tblSS_Milestone.idSnapShot
WHERE (dbo.tblSS_Milestone.CF_Milestone_Type = 'GICAP') AND (SUBSTRING(dbo.tblSS_Milestone.sTask, 1, 1) = 'M') AND
(isnumeric(SUBSTRING(dbo.tblSS_Milestone.sTask, 2, 1)) = 1)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-03-28 : 20:36:36

You may not like to hear this, but here you go...

As you no-doubt suspect, this is giving you a bit of a red-herring error message. The question you have to ask yourself is when is '5' not 5? If it really were a 5, then you could validate it with something like this:

DECLARE @Value NVARCHAR(1)
SET @Value = N'5'

SELECT CONVERT(INTEGER, @Value) AS IntValue

First, I would suggest you try to nail down exactly which column contains the bad value by commenting out all the columns in your select clause except one, and go through one at a time until you find the one that is trouble. Then, follow that up with filtering out those rows that do not contain 5 (e.g. SELECT ... WHERE MyField NOT LIKE '%5%')

Then run some checks, perhaps using LEN() and DATALEN() statements to try to nail down a situation where it LOOKS like the field is 5, but in reality it is something like 5 followed by a non-visual high ASCII character, or something like that.

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources