I'm trying to use the BETWEEN function to exclude dates that fall between two parameter dates, but my current code is also excluding NULL occurrences. I have a query that includes account records that may or may not contain a 'Closed Date'. My current code partially works, but I don't want it to exclude NULL records that don't contain a Closed Date at all.
WHERE ta.ROW_PROCESSED_THRU = '20190731'
AND NOT CONVERT (VARCHAR(10),ta.CLOSED_DATE,112) BETWEEN '20190701' AND '20190731'
To include the NULLs you need to add an explicit NULL predicate. Also, the convert function may significantly degrade your query's performance, much better would be to explicitly state the date range with two predicates:
AND ( NOT (ta.CLOSED_DATE >= '20190701' AND ta.CLOSED_DATE < '20190801')
OR ta.CLOSED_DATE IS NULL)
HTH
If you want to include null you can map it to a value outside the range that's exluded, i.e.:
WHERE ta.ROW_PROCESSED_THRU = '20190731'
AND COALESCE(ta.CLOSED_DATE, '00000000') NOT BETWEEN '20190701' AND '20190731'
AND (NOT (ta.CLOSED_DATE >= '20190701' AND ta.CLOSED_DATE < '20190801')
OR ta.CLOSED_DATE IS NULL)
