How to select varchar field with dates not earlier than today

Question

Hi. The first two lines of the following WHERE clause work fine. The problem is the last line.

I'm trying to select records with the field TERMINATION_DATE not earlier than today's date. TERMINATION_DATE is varchar(50) and is null for most records. I have tried several attempts with the latest being the line below. I appreciate your help!

WHERE [CARE_ENROLLMENTS_CARE_PROGRAM_NAME] NOT LIKE '%FCSB%'
AND CARE_PLAN_STATUS = 'Active'
AND CONVERT(DATE, TERMINATION_DATE) NOT < CONVERT(GetDate() as DATE)

Answer 1

Sql queries are based on set theory. In set theory null is an unknown value, so any comparison to null is always false. For example

Null < 10. = false
Null > 10 = false
Null = Null = false
Null not = Null = false

Sql added a special operator to test for null

Null is Null = true.

So, in your case you need to pick a value to use for null, use isnull or coalesce.

Isnull(null,0) < 10 = true