In my SQL Server query, I have this
to < GETDATE() or GETDATE() < from
Is it possible to simplify this with a BETWEEN operator?
Asked
Active
Viewed 129 times
-4
-
should be and instead of or if you want between logic – dportman May 02 '18 at 17:41
-
in this case, it has to be `or`. Then I assume this cannot be simplified? – omega May 02 '18 at 17:41
-
You cannot simplify that. Assuming that `from < to` you have to do two different queries. – Daniele Cappuccio May 02 '18 at 17:43
-
If it has to be `OR` logic, then no, you cannot simplify this with a `BETWEEN` operator. You can use `BETWEEN` in another way of expressing the same logic, but it will be less simple, not more simple. – Tab Alleman May 02 '18 at 17:44
-
Please provide the full sql statement – Daniel Marcus May 02 '18 at 18:28
3 Answers
1
Yes but (1) there is no query plan benefit in doing so, and (2) that also depends on your requirements. For more details than you probably care to read check out my buddy's article Beware of Between.
For example, from the article.. If I drive my car between 2 trees I don't want to include the trees (else I have tree damage and/or car damage). But if I catch a jet between New York and London, both cities are included (to catch the jet and to land the jet). So a "car between trees" is different to a "jet between cities"; in SQL 'between' is used in the 'inclusive' sense of "jet between cities".
Also, going off on a little bit of a tangent, do you really need the time component in your statement? If not CAST(GETDATE() as date) will return just the date part.
Jim Horn
- 879
- 6
- 14
1
Just to illustrate my comment, here is how you could rewrite your current filter using BETWEEN.
I am going to not assume that there are any rules about the values in [from] and [to], either could be less than the other, or they could be equal. If there were any such rules, then possibly this could be simplified.
And actually, in thinking this through enough to write it out, I see that the logic isn't as complicated as I originally thought. However I still wouldn't call it "simplifying" what you already have.
WHERE
([from] <= [to] AND GETDATE() NOT BETWEEN [from] AND [to]) --in this case it's a simple NOT BETWEEN
OR
([from] > [to]) --if [from] is greater than [to] then actually every possible value of getdate must be either greater than [to] or less than [from]
Tab Alleman
- 31,483
- 7
- 36
- 52
