In this video we’ll walk you though how to use the SQL BETWEEN operator; we’ll show you why you would want to use the BETWEEN operator and how to avoid pitfalls when using it with the DATETIME type.
The BETWEEN operator is equivalent to using AND combination of >= and <= comparison operators, but makes for a more compact range comparison. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server!
Once you have watched the video check out the sample code below. I’ve also included a transcript for you to use.
This is an SQL Minute on the BETWEEN Operator!
Welcome to another Essential SQL Minute. In this episode, we’re going to learn how to use the BETWEEN operator to compare a range of values in SQL server queries.
The BETWEEN operator is used to compare a range of values. Here’s an example where I’m using it to compare a range of values that are greater than or equal to 12, and less than or equal to 28. When I use the BETWEEN operator, it’s inclusive.
It will include the numbers that we’re comparing in between of. The common form for using BETWEEN in the WHERE clause is to specify the field, so in this case it’s “IsoNumericCode”, that’s the field. Then the BETWEEN operators; we’re saying we want the “IsoNumericCode” to be BETWEEN and then the range. So we want it to be between 12 and 28.
You may be wondering what this would look like the old fashioned way using greater than and equal to and less than and equal to. This is what it would look like. If we were to do the same thing using greater than and equal to and less than and equal to, we would have where IsoNumericCode >=12 AND IsoNumericCode <= 28.
Alright, so let’s go in to see what this looks like in SQL server. I have our query loaded up in SQL server. You can see here that we are going to look for the IsoNumericCode BETWEEN 12 and 28, it’s on the same line, it’s typically how it would be written.
SELECT CountryID
,CountryName
,IsoAlpha3Code
FROM Application.Countries
WHERE IsoNumericCode BETWEEN 12 and 28
Let me run that, and bring this up a little here. You can see that the range of values coming back are from 12 to 28. If I was to increase this let’s say to 100, I would expect to see more rows come back. You can see that they go all the way up to 100.
Now, I want to warn you that order does matter. If I did BETWEEN 100 and 12, I will get no results.
Because, if you think about it, what this is saying is I want the numeric codes to be between … I want it to be greater than or equal to 100, and less than or equal to 12. There’s nothing that’s in between that. So it’s a fault nothing comes back. So order matters, and it’s important that you have it so that using the BETWEEN operator, the lowest number is first. Alright, let’s go back to 12 and 28.
Another thing I can do is use NOT operator with BETWEEN. So if I say “NOT BETWEEN 12 and 28” what I’m really saying is let’s bring back every row in the countries table that doesn’t have a numeric code that’s greater than or equal to 12, and less than or equal to 28.
SELECT CountryID ,CountryName ,IsoAlpha3Code FROM Application.Countries WHERE IsoNumericCode NOT BETWEEN 12 and 28
So essentially, these four rows would be carved out of that result. Let’s run this. You can see now I’m getting 186 rows.
If I just did the query outright without any restrictions on it, you’ll see that I get 190 rows back. So this serves to show you that those four rows were indeed being carved out. In fact, if you want to do another test I could just say OR IsoNumericCode BETWEEN 12 and 28 and this would bring back everything.
SELECT CountryID
,CountryName
,IsoAlpha3Code
FROM Application.Countries
WHERE IsoNumericCode NOT BETWEEN 12 and 28
OR IsoNumericCode BETWEEN 12 and 28
So this brings back 190 rows. Really, this is kind of nonsensical in the sense that it’s bringing back all the rows.
All of our examples so far have been working with the integer. But BETWEEN works with many other data type. We also could use it for character data types (VARCHAR), and it would work the same way just as long as we have the collation orders set, everything works fine.
One data type though that I think you need to be careful of is the “DateTime” data type.
Because in the DateTime data type, you’ll see that there’s a time component in the date, and if we just do a BETWEEN using only the day and not the time, the date and time will default to midnight. It’s really not getting all the results you think it would. I think the best way to do this is to show you what I mean.
DECLARE @myTable TABLE(Name varchar(40),
ModifyDate DateTime)
INSERT INTO @myTable (Name, ModifyDate) VALUES ('Tom', '2017-02-01');
INSERT INTO @myTable (Name, ModifyDate) VALUES ('Tom', '2017-02-01 08:23:42');
INSERT INTO @myTable (Name, ModifyDate) VALUES ('Tom', '2017-02-01 14:04:02');
INSERT INTO @myTable (Name, ModifyDate) VALUES ('Tom', '2017-02-03');
INSERT INTO @myTable (Name, ModifyDate) VALUES ('Tom', '2017-02-03 22:32:43');
I’m going to bring in an example where we’re going to declare a table, and in this table we’re going to pull in dates from February 1st through the 3rd. When I run this, you’re going to see that I get five rows back.
Notice how there’s times on these, right? If I don’t specify a time on a date, it comes back with a date starting at midnight. So far, so good.
The big question would be so what’s going to happen then on another query here, if I bring back “ModifyDate” BETWEEN the 1st and the 3rd?
If I say that in English, I would expect all five of these to come back. But I’m going to say right now that not all five are going to come back, because this one right here that has the 3rd on it, when it gets converted to the DateTime will actually have a time of midnight. So it’s going to drop that 5th row. Let me show you what I mean here.
SELECT Name,
ModifyDate
FROM @myTable
WHERE ModifyDate BETWEEN '2017-02-01' AND '2017-02-03'
ORDER BY ModifyDate
As you can see, now it’s basically bringing back only four rows because we’re looking for in essence dates and times that are between midnight of the 1st and roughly midnight of the 3rd.
One way that you can work around this is to put in the time just before midnight. So just to … I’ll go out to the seconds here. So now when I run this you’ll see that I get five rows because it’s going to “11:59:59” seconds.
SELECT Name, ModifyDate FROM @myTable WHERE ModifyDate BETWEEN '2017-02-01' AND '2017-02-03 23:59:59' ORDER BY ModifyDate
I’m not counting the hundreds of seconds and so on. To be honest, I feel that that’s kind of messy. I feel if you really want to use BETWEEN with dates in this case, and you wanted to get every date possible with its time from the 3rd, the best way to do that is just to use the 4th.
SELECT Name, ModifyDate
FROM @myTable
WHERE ModifyDate BETWEEN '2017-02-01' AND '2017-02-04'
ORDER BY ModifyDate
In this case, BETWEEN with DateTime isn’t necessarily using the same behavior as we would think it would with integers, because though it’s inclusive, this is going to go to midnight, which is just that nanosecond past the 3rd. It just looks funny. When I run this, you’ll see it does pull in that date from the latter part of the 3rd.
So that is a gotcha with BETWEEN and DateTime. So I know a lot of people kind of avoid BETWEEN with dates and time, and they’ll just use greater than and less than.
So it begs the question; why would you use BETWEEN?
I think there’s two reasons why you would want to use BETWEEN. The first is readability. The second is maintainability.
For readability, what I mean is that if I had to say we’re “ModifyDate” is greater than and equal to the 1st, and “ModifyDate” is less than equal to the 4th, in a very long query this may get hard to read because it’s a lot of expressions to start to parse through, and the Boolean logic may get in my way. In that sense, BETWEEN might seem a little more natural to read. At the risk of misunderstanding what it’s actually doing, as we’ve explained with DateTime.
Also, from a maintainability point of view, if I want to go in and I need to change like “ModifyDate” to, I know I can come in here and I have to change it in two places. I’d have to change the first part of the term and the second part, hypothetically. Where if I’m just using BETWEEN, I need only change it in one place.
If you’d like to know my take on whether you would use BETWEEN or greater than or less than, I personally use greater than or less than, because then I know what action is actually being taken. I feel I have a little more explicit say in what’s going on, and that makes me feel better. I’m not having to worry about, “Is that really working? Is it taking the date before midnight?” All that kind of assumption stuff goes out the window. I like working where there’s no assumptions in computers, it just makes me feel more happy. I personally don’t use BETWEEN. But if you like it, I think it’s great to use. It’s supported in many different SQL dialects, and I say go for it. Thanks again, have a good day.
