MS SQL - Convert string mm/dd to date
The best thing that you can do is fix your table so that you store a date instead of a string. With that said, the code that you posted in the question is on the right track. The problem is that you leave it as a string and don't explicitly cast it to a DATETIME. By the rules of data type precedence the data that you filter against also gets converted to a string which isn't what you want.

If you want to keep the SUBSTRING approach then you can try the following:

CREATE TABLE #T (NOT_A_DATE NVARCHAR(5));

INSERT INTO #T VALUES (N'01/30'), (N'01/31');

SELECT
    DATEFROMPARTS(
      datepart(year, getdate())
    , substring(NOT_A_DATE, 1, 2) 
    , substring(NOT_A_DATE, 4, 5)
)
FROM #t;


The key is the use of the DATEFROMPARTS function to convert the string to a date that's safe in all locales.