Error converting data type varchar to bigint: INSERT INTO SELECT

I keep getting this error when trying to execute a stored procedure and am not sure why:

Msg 8114:
Error converting data type varchar to bigint.

Please see my SQL query below:

INSERT INTO StagingArea.dbo.DimStudentsTEST
       (StudentCode, Module, Year, UniqueStudentID)
    SELECT DISTINCT
        RTRIM(S.STUDENT_Student_ID) + 
            RTRIM(SUBSTRING(AY.ACADEMYR_Academic_Year_Code, 3, 2) +
            SUBSTRING(AY.ACADEMYR_Academic_Year_Code, 8, 2)) AS 'UniqueStudentID',
        ...
    FROM
        ...
    INNER JOIN
        ...

I believe the error is emerging because of the UniqueStudentID I have tried using CAST & CONVERT around the SELECT line but still no luck. Perhaps I am using in the wrong way. I have a feeling it is maybe because the column of the table I am pulling the data from (“AY.ACADEMYR..”) is not of datatype ‘bigint’ so the error message keeps occuring. The datatype of the ‘UniqueStudentID’ column is of datatype ‘bigint’

Can anyone see where the problem lies or if I am meant to use the CAST/CONVERT function then how best to use in this scenario.

Many thanks,

Answer

Assuming the columns are in the proper order during the INSERT.

I suspect you there may be some unexpected data/strings in the underlying components.

To identify the bogus records, try the following using try_convert(). As you may know, try_convert() will return a NULL if the conversion fails.

Example

Select *
 From  ...
 Where try_convert(bigint,RTRIM(S.STUDENT_Student_ID)+RTRIM(SUBSTRING(AY.ACADEMYR_Academic_Year_Code,3,2)+SUBSTRING(AY.ACADEMYR_Academic_Year_Code,8,2))) is null