[Solved] How to use the between operator in SQL Server?

Question

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]

Accepted Answer

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]