-3

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'
Improve this question
1

3 Answers 3

Reset to default
-3 
 AND (NOT (ta.CLOSED_DATE >= '20190701' AND ta.CLOSED_DATE < '20190801') 
    OR ta.CLOSED_DATE IS NULL)
Improve this answer
4
  • 2
    You should add some detail to your answer showing why BETWEEN is terrible for this versus your proposed change.
    – Hannah Vernon ♦
    Commented Aug 1, 2019 at 20:08
  • I was initially getting an error related to the "AND", but it was just because the first parentheses should be after the "AND" and not before it. AND (NOT (CONVERT(VARCHAR(10),tm.DATE_CLOSE, 112) BETWEEN @strFirstofMonth AND @strLastofMonth) OR tm.DATE_CLOSE IS NULL)
    – Daylon Hunt
    Commented Aug 1, 2019 at 20:30
  • 2
    The AND should be outside the parentheses, not inside.
    – ypercubeᵀᴹ
    Commented Aug 2, 2019 at 6:27
  • 1
    @Mike you have 2 answers without using CONVERT. Why do you insist on its use? What's the type of the column?
    – ypercubeᵀᴹ
    Commented Aug 2, 2019 at 13:34
3

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

Improve this answer
3
  • I should clarify, that I DO want to include NULL occurrences. Some records may contain a Closed Date, and some may not. I just want to exclude occurrences when the Closed Date is between the two parameter dates, while still INCLUDING records with a NULL Closed Date or a Closed Date that's outside of the two parameter dates
    – Daylon Hunt
    Commented Aug 1, 2019 at 18:33
  • For some reason this was only filtering in just the NULL occurrences, which could have been an error on my part
    – Daylon Hunt
    Commented Aug 1, 2019 at 20:33
  • 2
    @ypercube thanks for the edit ! :-) <thumb up>
    – SQLRaptor
    Commented Aug 3, 2019 at 0:23
0

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'
Improve this answer
4
  • A little confusing and I ultimately wasn't able to get it to work correctly, but it was likely due to my inexperience
    – Daylon Hunt
    Commented Aug 1, 2019 at 20:34
  • Your secret is safe with us, what problem did you encounter?
    – Lennart - Slava Ukraini
    Commented Aug 1, 2019 at 20:55
  • Watch out using 0 as a placeholder for the epoch. While 0 is the epoch for datetime, it won't work for datetime2, but having one argument as 0 will cause the engine to try and coerce closed_date (in this example) into a datetime, which may or may not work since datetime2 can hold values outside the range of a datetime. see dba.stackexchange.com/questions/208741/…
    – Jonathan Fite
    Commented Aug 2, 2019 at 12:31
  • I beleive this is a string, and its not used for anything but as a value guaranteed outside the intervall. I used '0000....' but could have used '15000101' equally well
    – Lennart - Slava Ukraini
    Commented Aug 2, 2019 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.