CASE WHEN DATE not providing correct results

Question

When using the below query the "Result_field" returns "NO" even if the date is greater than 12/31/2020. How can I get it to show "YES" when the date is greater than 12/31/2020 and "NO" When Less Than? Been trying to research this for a while and now need help. This may be a simple query, but I'm kinda new at this. Thanks in advance.

SELECT Date_field,
CASE 
    WHEN Date_field < '2020-12-31' THEN 'NO' ELSE 'YES'
    end as Result_field
FROM Yearly_Report;

This is the result I get:

Answer 1

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') < '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