NOT BETWEEN is tricky and misleading

Transact-SQL
1

I have a query in which I am using NOT BETWEEN, but I believe this is not brining in the correct results that I need.

Think of if someone were to say pick a number between 1 and 10. The majority would consider the numbers 1 and 10 to be acceptable within the pick range. But it seems like that SQL doesn't think so. Having an actual_date of 4/29 should NOT be an exception when in fact it is within the date range of 4/28 and 5/1.

Does anyone have an idea on how to get around this? My query is below as well as the end result.
You will notice how the first record is inaccurate as I mentioned above.

SQL Query used:

SELECT repairjob.repairjobno,
repairjob.actual_date,
repairjob.repairstartdate,
repairjob.repairenddate,
CASE WHEN ((actual_date NOT BETWEEN repairstartdate AND repairenddate) OR
(repairstartdate IS NULL)) THEN 'Not within the repair job date range'
ELSE '' END AS exception

FROM repairjob

WHERE ((CASE WHEN ((actual_date NOT BETWEEN rpeairstartdate AND repairenddate) OR
(repairstartdate IS NULL)) THEN 'Not within the repair job range' ELSE '' END) <> '')

Results from Query:

repairjobno	actual_date	repairstartdate	repairenddate	exceptionreason
103		2015-05-27	2015-05-26	2015-05-28	Not within the repair job date range
105		2015-06-02	2015-06-07	2015-06-08	Not within the repair job date range
111		2015-08-28	2014-07-27	2014-07-29	Not within the repair job date range
110		2015-08-28	2014-07-27	2014-07-29	Not within the repair job date range
106		2015-08-28	NULL		NULL		Not within the repair job date range
109		2015-08-28	2014-07-27	2014-07-29	Not within the repair job date range
113		2015-08-28	2014-07-27	2014-07-29	Not within the repair job date range
2

You've got some other issue here, since BETWEEN is indeed inclusive on both the starting and ending values.

What are the data types of columns: actual_date, repairstartdate and repairenddate?

3

If you change the WHERE clause to the following, do you get the answers you expect? (My guess is that you will still get the same results)

WHERE  
	actual_date < rpeairstartdate 
	OR actual_date > repairenddate
	OR repairstartdate IS NULL
4

Scott, the data types for all 3 are datetime

5

JamesK

correct, I do get the same results by trying your suggestion. It really has been tough to try and avoid this dilemma.

6

Then you're not showing the result from the query. Or that's not the query. A datetime column would show the time when it was displayed.

At any rate, the WHERE condition as written would exclude any values that were within the range. For example:

SELECT repairjob.repairjobno, 
repairjob.actual_date,
repairjob.repairstartdate,
repairjob.repairenddate,
CASE WHEN ((actual_date NOT BETWEEN repairstartdate AND repairenddate) OR
(repairstartdate IS NULL)) THEN 'Not within the repair job date range' 
ELSE '' END AS exception
FROM (
    SELECT 103 AS repairjobno, CAST('20150527' AS datetime) AS actual_date,
        CAST('20150526' AS datetime) AS repairstartdate, CAST('20150528' AS datetime) AS repairenddate
)
AS repairjob
WHERE ((actual_date NOT BETWEEN repairstartdate AND repairenddate) OR (repairstartdate IS NULL))
7

Scott,

Yes, you are correct. I converted the datetime to just date using CAST, but it's not in the query and the reason for me not putting it in is because I the CONVERT didn't make a difference on my results. I had thought that it may help with using the NOT BETWEEN but it didn't.
My apologies for the confusion/inconvenience.

8

Scott,
Here is the results where I removed the CAST from the dates

repairjobno	actual_date		repairstartdate		repairenddate		exceptionreason
103		2015-05-27 00:00:00.00	2015-05-26 05:00:00.00	2015-05-28 05:00:00.00	Not within the repair job date range
105		2015-06-02 00:00:00.00	2015-06-07 05:00:00.00	2015-06-08 05:00:00.00	Not within the repair job date range
111		2015-08-28 00:00:00.00	2014-07-27 05:00:00.00	2014-07-29 05:00:00.00	Not within the repair job date range
110		2015-08-28 00:00:00.00	2014-07-27 05:00:00.00	2014-07-29 05:00:00.00	Not within the repair job date range
106		2015-08-28 00:00:00.00	NULL		NULL				Not within the repair job date range
109		2015-08-28 00:00:00.00	2014-07-27 05:00:00.00	2014-07-29 05:00:00.00	Not within the repair job date range
113		2015-08-28 00:00:00.00	2014-07-27 05:00:00.00	2014-07-29 05:00:00.00	Not within the repair job date range
9

Again, somthing else MUST be going on. SQL is not getting a simple NOT BETWEEN "wrong". Would have to see the actual code, and actual input data, to debug any further.

10

I get it to work with:

DROP TABLE #repairjob; 
CREATE TABLE #repairjob (repairjobno VARCHAR(10), actual_date DATETIME, repairstartdate DATETIME, repairenddate DATETIME);
INSERT INTO #repairjob VALUES 
('103','2015-05-27','2015-05-26','2015-05-28'),
('105','2015-06-02','2015-06-07','2015-06-08'),
('111','2015-07-28','2015-07-27','2015-07-29'),
('110','2015-08-28','2014-07-27','2014-07-29'),
('106','2015-08-28',NULL,NULL),
('109','2015-08-28','2014-07-27','2014-07-29'),
('113','2015-08-28','2014-07-27','2014-07-29');

SELECT repairjobno, actual_date, repairstartdate, repairenddate,
CASE WHEN ((actual_date NOT BETWEEN repairstartdate AND repairenddate) OR
    (repairstartdate IS NULL)) THEN 'Not within the repair job date range' 
    ELSE '' END AS exception 
FROM #repairjob 
WHERE ((CASE WHEN ((actual_date NOT BETWEEN repairstartdate AND repairenddate) OR
 (repairstartdate IS NULL)) THEN 'Not within the repair job range' ELSE '' END) <> '')

Which gives: Notice I did not get 103 or 111 (I changed the date to be between)

repairjobno	actual_date	repairstartdate	repairenddate	exception
105	2015-06-02 00:00:00.000	2015-06-07 00:00:00.000	2015-06-08 00:00:00.000	Not within the repair job date range
110	2015-08-28 00:00:00.000	2014-07-27 00:00:00.000	2014-07-29 00:00:00.000	Not within the repair job date range
106	2015-08-28 00:00:00.000	NULL	NULL	Not within the repair job date range
109	2015-08-28 00:00:00.000	2014-07-27 00:00:00.000	2014-07-29 00:00:00.000	Not within the repair job date range
113	2015-08-28 00:00:00.000	2014-07-27 00:00:00.000	2014-07-29 00:00:00.000	Not within the repair job date range
11

There is an issue with DATETIME if you say BETWEEN 4/28 and 4/29 - a DATETIME value of 1-minute-past-midnight on 4/29 is NOT between 4/28 and 4/29.

Typical solution to this is to say:

    MyDateTimeColumn >= '20150428'
AND MyDateTimeColumn <  '20150430' -- i.e. less-than one PAST the limit
12

Kristen, thanks for pointing that out, it totally slipped my mind as I was focused on the result not the mechanics. :slight_smile:

1 Like