CASE WHEN DATE not providing correct results
Without seeing the table definition, this is kind of an educated guess, but it looks like your Date_Field is a string field (VARCHAR, NVARCHAR etc) instead of a DATE or DATETIME field. 

As per this fiddle, when defined as a VARCHAR field the results are all "NO" because the comparison is a string to string and doesn't make any sense (one string cannot be less than another string). When defined as a DATE field, the comparison value is implicitly converted to a DATE value and then can be successfully compared.

The correct solution would probably be to change the data type of the Date_Field column to DATE or DATETIME, however, if this isn't possible you can resolve this in the query alone using TRY_PARSE:

SELECT DateField,
CASE 
    WHEN TRY_PARSE(DateField AS DATE USING 'en-US') &lt; '2020-12-31' THEN 'NO' ELSE 'YES'
    end as ResultField
FROM Dates;


You can see the results in the fiddle are as expected:

DateField   ResultField
-----------------------
06/30/2022  YES
06/30/2018  NO
12/31/2022  YES
06/30/2020  NO
09/30/2020  NO
03/31/2020  NO
03/31/2024  YES
06/30/2021  YES