CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008type-conversionvarchar
Aruna Raghunam
Aruna Raghunam

Reputation: 921

Conversion failed when converting varchar value to data type int error in Visualstudio

Query:

SELECT CAST(College_Year AS VARCHAR(10)),
       Learner_Id,
       Forename_1,
       Surname,
       sum(Possible_Duration) AS Possible_Duration,
       sum(Present_Duration) AS Present_Duration,
       CASE
           WHEN sum(Present_Duration) = 0 THEN 0
           ELSE sum(Present_Duration) / sum(Possible_Duration)
       END AS Attendance
FROM dbo.IM_AT_Attendance_vw
WHERE Session_date >=
    (SELECT ACYR_Start_Date
     FROM dbo.ACYR
     WHERE dbo.IM_fnCurrentACYR() = ACYR_College_year)
  AND Session_Date <=
    (SELECT ACYR_End_Date
     FROM dbo.ACYR
     WHERE dbo.IM_fnCurrentACYR() = ACYR_College_year)
  AND Session_Date NOT IN
    (SELECT Holiday_Date
     FROM IM_EFC_Holidays)
  AND Possible = 1
  AND month(Session_Date) = month(DATEADD(mm, -1, GETDATE()))
  AND Learner_ID = @Learner_ID
GROUP BY College_Year,
         Learner_Id,
         Forename_1,
         Surname
ORDER BY Surname,
         Attendance

above query works fine in SSMS but when I execute from visual studio throws an error:

Conversion failed when converting varchar value '414084H' to datatype int.

I am guessing problem with LearnerID column - datatype varchar(12)

I tried CAST(Learner_ID AS INT) and CONVERT(INT, Learner_ID) but doesn't help....

Any help is much appreciated.

Thanks,

Ar

Upvotes: 1

Views: 2499

Answers (1)

Patricia
Patricia

Reputation: 71

Your LearnerID value "414084H" contains a letter at the end. Therefore it cannot be converted to integer.

If the format is alyways the same you can select just the first 6 characters from your value in the select, and this way the conversion will succeed:

select SUBSTRING(LearnerID, 0, 6)

Upvotes: 1

Related Questions