CONVERT fails but TRY_CONVERT successfully convert same data

I have a column with dates stored as strings (NVARCHAR(500)), in dd/MM/yyyy format.

This query gets all rows with a non null value in my date_column, 13955 rows returned, 18359 total rows on table.

SELECT ID, date_column
FROM [MyTable] WITH(NOLOCK)
WHERE
    client = 178425
    AND folder = 1422720
    AND date_column IS NOT NULL

Filtering for rows that successfully converted the string to a date value using TRY_CONVERT. Returned all 13955 rows, indicating all of them are valid dates.

SELECT ID, date_column
FROM [MyTable] WITH(NOLOCK)
WHERE
    client = 178425
    AND folder = 1422720
    AND date_column IS NOT NULL
    AND TRY_CONVERT(DATE, date_column, 103) IS NOT NULL

Now converting the column to a date in the select statement, again no errors and all 13955 rows returned.

SELECT ID, CONVERT(DATE, date_column, 103)
FROM [MyTable] WITH(NOLOCK)
WHERE
    client = 178425
    AND folder = 1422720
    AND date_column IS NOT NULL
    AND TRY_CONVERT(DATE, date_column, 103) IS NOT NULL

So I assumed all NON-NULL values are valid dates, right?

But then if I remove the TRY_CONVERT from the where statement, I receive an error:

Conversion failed when converting date and/or time from character string.

SELECT ID, CONVERT(DATE, date_column, 103)
FROM [MyTable] WITH(NOLOCK)
WHERE
    client = 178425
    AND folder = 1422720
    AND date_column IS NOT NULL

How can this be? ALL Non NULL rows are valid dates but only if I use TRY_CONVERT function there? Any ideas on how to find the faulty values in my table?

PS. Not providing sample data because all tests I did out of this specific table worked as expected. I have no Idea what is causing this problem...

Solution

This is essentially Martin Smith's comment. I believe it deserves to be an answer. Because it is the answer.

convert can fail even in rows that your select never returns due to where.

In contrast, try_convert never fails, and because your where eliminates problematic rows, you are left wondering what the title says.

For OP's post, it means that some client/folder combination other than 178425/1422720 has a [date_column] which cannot be converted.