How to select varchar field with dates not earlier than today

Allan, Michael 21 Reputation points
2022-01-21T15:04:15.387+00:00

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)

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 57,891 Reputation points
    2022-01-21T15:34:51.94+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful