Right function with character and integer

I have a SELECT statement where I need to compare the month of a date field to the 2 left characters in a VarChar field. I can’t seem to figure out how to do this. So I broke the 2 pieces out and ran the following code (I simplified it by removing fields irrelevant to this issue):

SELECT DISTINCT
  .
  .
  .
    '0' & Month(MaxOfRT_RENL_DT) as RT_RENL,
    Left(DED_PERIOD_START, 2) as RT_Start
FROM AS_tblTBMED;
--WHERE Right('0' & Month(MaxOfRT_RENL_DT), 2) <> Left(DED_PERIOD_START, 2);

When I run this, I get "0" in the RT_RENL field. I’m guessing it’s a CAST issue, but I’ve been away from SQL for so long I can’t recall how to do this.

Sample data would look like this:

MaxOfRT_RENL_DT       DED_PERIOD_START
01-01-2023                0101
02-01-2023                0201
03-01-2023                0301

>Solution :

You need to use the operator + and cast the function month’s returned value into varchar.

SELECT  '0' + cast( Month(MaxOfRT_RENL_DT) as varchar) as RT_RENL,
    Left(DED_PERIOD_START, 2) as RT_Start
FROM AS_tblTBMED;

With the where condition can be :

SELECT  '0' + cast( Month(MaxOfRT_RENL_DT) as varchar) as RT_RENL,
    Left(DED_PERIOD_START, 2) as RT_Start
FROM AS_tblTBMED
WHERE '0' + cast( Month(MaxOfRT_RENL_DT) as varchar) = Left(DED_PERIOD_START, 2);

As mentioned by @stu in the comment you can also cast DED_PERIOD_START to an int and compare it with month() :

SELECT  Month(MaxOfRT_RENL_DT) as RT_RENL,
    CAST(Left(DED_PERIOD_START, 2) AS int) as RT_Start
FROM AS_tblTBMED;

Demo here