SQL 2005 - Conversion failed when converting the varchar value '6.2' to data type int.

    Question by:
    On

    Topics:

    Please note, the SQL is handled dynamically by the server, therefore some items in my WHERE clause will look odd to you - please disregard this. I am getting the following error and need a hand.

    Msg 245, Level 16, State 1, Line 113
    Conversion failed when converting the varchar value '6.2' to data type int.

    SET NOCOUNT ON
     
    CREATE TABLE #Patients (
    	PatientProfileID int,
    	PatientID varchar(15),
    	MRN varchar (15),
    	PID numeric,
    	PatientName varchar(100),
    	Birthdate datetime,
    	Age varchar (15),
    	Sex varchar(1),
    	RaceCode varchar(50),
    	EthnicityCode varchar(50),
    	RaceMID2 int
    )
     
    DECLARE @AgeDate datetime
    DECLARE @DXDate datetime
     
    SET @AgeDate = '12/31/' + CONVERT(varchar, YEAR('08/31/2009'))
    SET @DXDate = '06/30/' + CONVERT(varchar, YEAR('08/31/2009'))
     
    INSERT INTO #Patients
    SELECT pp.PatientProfileID, 
    	pp.PatientID,
    	pp.MedicalRecordNumber AS MRN,
    	pp.PID,
    	RTRIM(RTRIM(ISNULL(pp.Last, '') + ' ' + ISNULL(pp.Suffix, '')) + ', ' + ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) AS PatientName,  
    	pp.Birthdate,
    	(DATEDIFF(day,pp.Birthdate,@AgeDate) /365.25) as Age, 
    	pp.Sex,
    	CASE WHEN r.Code = 'H' OR e.Code = 'H' THEN 'Hispanic/Latino' 
    		WHEN r.Code = 'A' THEN 'Asian'
    		WHEN r.Code = 'NH' THEN 'Native Hawaiian'
    		WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
    		WHEN r.Code = 'B' THEN 'Black/African American'
    		WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
    		WHEN r.Code = 'W' THEN 'White'
    		WHEN r.Code = 'M' THEN 'More than one race'
    		WHEN r.Code = 'U' THEN 'Unreported'
    		ELSE 'Unreported' END AS RaceCode,
    	CASE WHEN r.Code = 'H'  OR e.Code = 'H' THEN 'Hispanic/Latino' 
    		ELSE 'All Others' END AS EthnicityCode, 
    	cri.RaceMID2
     
     
    FROM PatientProfile pp
    	LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
    	LEFT JOIN cusCRIInterview cri on pp.patientprofileid = cri.patientprofileid
    	LEFT JOIN Medlists r on pp.RaceMID = r.MedListsID
    	LEFT JOIN cusCRIMedLists e on cri.RaceMID2 = e.MedListsID
    	
    WHERE 
           --Filter on facility
    	(
    	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
    	(NULL IS NULL)
    	)
         	 AND 
    	--Filter on Company
    	(
    	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
    	(NULL IS NULL)
    	)
    	AND
    	(DATEDIFF(day,pp.Birthdate,@AgeDate) /365.25) >17.99
    	AND 
    	DATEPART(Year,pv.visit) = DATEPART(Year,'08/31/2009') -- limit to visits in this year
    	AND
    	pp.PatientProfileID in -- now we filter on diagnosis, independently
    	(
    		SELECT PatientProfileID
    		FROM PatientProfile pp
    		JOIN Orders o ON pp.PID = o.PID
    		JOIN Orddx  ON o.dxgroupid = orddx.dxgroupid
    		WHERE
    		orddx.dxcode  LIKE ('icd-401.%') 
    		AND  o.XID=1000000000000000000
    		AND o.OrderDate <= @DXDate
    	)
    GROUP BY pp.PatientProfileID,pp.PatientId, pp.MedicalRecordNumber, pp.PID, pp.Birthdate, pp.Sex, pp.Last, pp.Suffix, pp.First, pp.Middle, r.code, e.code, cri.raceMID2
    HAVING COUNT(*)>1 -- must have 2+ visits this year
     
     
    SELECT PatientProfileID,
    	count(*) as cnt,
    	PatientID,
    	MRN,
    	PID,
    	PatientName,  
    	Birthdate,
    	Age, 
    	Sex,
    	RaceCode,
    	EthnicityCode, 
    	RaceMID2
    INTO #tmp
    FROM #Patients 
    GROUP BY PatientProfileID, PatientID, MRN, PID, PatientName, Birthdate, Sex, age, RaceCode, EthnicityCode, RaceMID2
    ORDER BY	CASE
    	WHEN '1'  = 1 THEN MRN
    	WHEN '1'  = 2 THEN PatientName 
    	WHEN '1'  = 3 THEN PatientID ELSE NULL
    	END
     
    DECLARE @StartDate Datetime
    DECLARE @EndDate Datetime
     
    SET @StartDate  = CONVERT(varchar, YEAR('08/31/2009')) + '/01/01'
    SET @EndDate = CONVERT(varchar, YEAR('08/31/2009')) + '/12/31'
     
    ALTER TABLE #TMP ADD ObsValue varchar (2000) null
    Update #TMP
    Set ObsValue = '1'
    FROM OBS obs
    JOIN #TMP ON obs.PID = #TMP.pid
    join (select pid, max(obsdate) as obsmax from obs WHERE ((HDID = 54 AND case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 140) OR (HDID = 53 and case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 90)) 
    and obsdate >= @StartDate AND obsdate <= @EndDate group by pid) t on obs.PID=t.PID and obs.obsdate=t.obsmax
    WHERE ((HDID = 54 AND case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 140) OR (HDID = 53 and case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 90))
     
     
    Select * FROM #TMP 
    Drop Table #tmp
    Drop Table #Patients
    
                                    
    1:
    2:
    3:
    4:
    5:
    6:
    7:
    8:
    9:
    10:
    11:
    12:
    13:
    14:
    15:
    16:
    17:
    18:
    19:
    20:
    21:
    22:
    23:
    24:
    25:
    26:
    27:
    28:
    29:
    30:
    31:
    32:
    33:
    34:
    35:
    36:
    37:
    38:
    39:
    40:
    41:
    42:
    43:
    44:
    45:
    46:
    47:
    48:
    49:
    50:
    51:
    52:
    53:
    54:
    55:
    56:
    57:
    58:
    59:
    60:
    61:
    62:
    63:
    64:
    65:
    66:
    67:
    68:
    69:
    70:
    71:
    72:
    73:
    74:
    75:
    76:
    77:
    78:
    79:
    80:
    81:
    82:
    83:
    84:
    85:
    86:
    87:
    88:
    89:
    90:
    91:
    92:
    93:
    94:
    95:
    96:
    97:
    98:
    99:
    100:
    101:
    102:
    103:
    104:
    105:
    106:
    107:
    108:
    109:
    110:
    111:
    112:
    113:
    114:
    115:
    116:
    117:
    118:
    119:
    120:
    121:
    122:
    123:
    124:
    

    Select allOpen in new window

    Good Question?
    0
     

    ?

    The member who asked this question verified this comment provided the solution that solved their problem.

    Accepted Solution on 2009-09-27 at 00:14:02ID: 25433025

    >Arithmetic overflow error converting varchar to data type numeric.

    that means you have numbers in that column that are bigger than the capacitiy of  decimal(20,4).
    ie:
    16 digits before the comma, 4 digits after the comma.
    please double-check your data, and increase as needed

    Top Expert Contributor

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201511-LO-Qu-074

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Brian Crowe

    1,663

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Visual Basic.NET
    • Query Syntax
    • Crystal Reports

    Éric Moreau

    2

    Articles

    10,373

    Solutions

    Expert in:

    • .NET Programming
    • Visual Basic.NET
    • C#
    • Visual Basic Classic
    • ASP.NET

    Vitor Montalvão

    11

    Articles

    2,158

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax
    • Databases-Other

    Paul Maxwell

    9

    Articles

    2,791

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Query Syntax
    • MS SQL Server 2005
    • Oracle Database

    ste5an

    1,059

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • .NET Programming
    • C#
    • Databases-Other

    geek_vj

    306

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008

    Lee Savidge

    1,023

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008
    • JavaScript
    • ASP

    Zberteoc

    908

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008
    • Query Syntax
    • Databases-Other

    deepakChauhan

    291

    Solutions

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005

    RELATED TOPICS view all topics

    1. MS SQL Server
      (154,731)
    2. MS SQL Server 2008
      (48,104)
    3. Query Syntax
      (46,110)
    4. Databases-Other
      (53,016)
    5. .NET Programming
      (131,147)
    6. ASP.NET
      (122,126)
    7. SSRS
      (8,530)
    8. Windows 2003 Server
      (129,268)
    9. DB Reporting Tools
      (7,908)
    10. Visual Basic.NET
      (91,458)