So moving right along our next operator will take a look at is called between and between is pretty
straightforward it allows us to select things based off of two values an upper and lower range.
So I can say I want to find all people who have a birthday between April 20th of 2017 and April 22nd
of 2017.
Or I want to find all books that have page counts between 105 hundred pages so we actually can do this
already using what you know.
So before I show you between, how can we accomplish the same thing using the tools that we already
have, things that we learned in this section already.
Because if you take a moment to think about what being between two values actually means, it's really
just a combination of greater than and less than.
So how do we combine two different conditions?
Well, logical.
And so let's try an example.
Again, just to reiterate, if I wasn't clear, we're doing it without the new between operator.
We haven't seen that yet.
We're doing it the longer way, if you will.
So select all books published between the years 2004 and 2015, and that includes 2004 and 2015, just
to be clear.
So to do that we use logical end to join together to different conditions, to components.
So select title and release gear, blah, blah, blah.
That doesn't matter from books where release here is greater than or equal to 2004 and release year
is less than or equal to 2015.
So both parts that have to be true meaning that whatever the book is that selected or all of the books
that are selected are somewhere between 2004, including 2004 in 2015, including 2015.
So I won't make you watch me type this whole thing.
I'm just going to paste it over here.
But let's start off by just doing select title and release year from books and just check just we know
this, but just to verify that there are things outside that range and now.
If we paste in using the ampersand, ampersand and operator, which of course could also be written
this way, if I could type correctly, you'll see that we get things that are between 2004 and 2015,
and we know that it is inclusive, as you can see, because 2004 is here.
And if we hadn't included that equal sign, then it would not be there.
So really what I'm trying to show you is that you don't need a fancy between operator to accomplish
this.
You can just do it using good old fashioned logic, using greater than less than and logical end.
However, there is an operator between that will make it easier.
So it looks like this.
The syntax is between and then some value and some other value.
This and here.
Just.
I get it.
It could be confusing when we also have this and and this one, but they are different.
So this end is paired with between always.
So here's an example.
Select title and release here again from books where released here between 2004 and 2015.
So this does the exact same thing as this right here.
But we don't have to use a greater than equal to, less than equal to.
We don't have to write release here twice.
All we have to do is say we're released here between 2004 and 2015.
And in my opinion, this is much easier to read, much easier to remember if you're trying to do this
between it's a pretty straightforward thing to recall.
It does what it sounds like.
So let's try it out.
Just copy it.
We'll bring it here.
So we have a record in the note and then I'll just paste it in select title release here from books
where release year between 2004 and 2015.
And as you can see, we get the same results that we did when we use this version.
It's just a little shorter and cleaner and easier to understand, in my opinion.
And again, it is inclusive.
We have 2004 there.
And one thing to keep in mind is that there may be times where you don't want it to be inclusive and
there's not an easy way just to do that.
Well, there is an easy way, but basically it just means change this number.
If you want it to be 2005 to 2014, you could do that.
But there's no easy way to do between non inclusive like here where we can just eliminate the equal
sign.
Okay, so one other thing is that not between is also a thing and it's pretty straightforward.
Basically, it does the opposite of between.
So we could say I want all the books that have a page length that is not between 105 hundred and that
gives us everything less than 100 and everything greater than 500.
So here's our same example.
If we do select title and release year from books where released year is not between.
So all that changes is we add OT before not between 2004 and 2015.
It takes that range from 2004 and 2015 and does everything outside of that.
So I'll just paste it here and then paste it in again.
And now you can see let's do an order by.
Now you can see we have everything up until 2004.
And then everything after 2015, but nothing from 2000 for up to 2015, they're gone.
These are just the leftovers.
So that's pretty much it for the basics of between and not between.
So before I let you go, there is one small side note I just want to bring up.
It's not crucial to understanding between, but it's just something that the documentation mentions.
So I figured I should also mention it.
If we go to the doctor between there is this note at the end.
For best results when using between with date or time values use cast to explicitly convert the values
to the desired data type.
So we haven't seen this cast.
We'll talk about it in a moment.
But what it lets us do is convert from one data type to another from a date to a date time, for instance,
or from a string of our char to a date.
For example, if you compare date time to to date values.
So we're trying to say if some date time is between two different dates, it's best to convert the date
values to also be date times.
Just so you're working with the same kind of consistent data type.
If you use a string constant such as 2001, dash one, dash one and it comparison to a date, it's better
to cast the string to a date.
So let's just take a look at how cast works first.
And it's pretty simple.
So let's take something like this.
2017 0502 That right there is not technically a date, right?
That's not considered a date data type.
It's just text, even though it looks like a date.
And it will it can be treated like one.
My SQL is smart enough to figure that out.
If we pass it into a date function like days or months or format date, but it's not actually a date.
But if we wanted to make it an actual date time, we can cast it and this will work its magic.
Now, just casting it like this won't do anything.
I have to say what to cast it as.
So let's cast it as a date time and we won't see anything unless we have select here.
So what this will do is it takes first thing whatever it is, and it tries to convert it to a date time.
So if there's just the number 99 or something, that will cause a problem.
But in this case, this is easily cast to a date time.
But you might be wondering, okay, the date part is easy to cast.
What about the time part?
Well, it just picks 0000 for you.
So it's now considered a date time.
So basically what the documentation is saying is if you're working with dates and you're trying to compare
them and you're working with between, for example, it's best to use a cast to make sure that all the
data types are the same.
Now, it actually this is kind of confusing.
It will work most of the time just fine if you don't, because my skill is smart enough to figure out
what you want, but it can cause problems, so it's just best to do that.
So I'm going to switch over to that testing database I made with this wonderful name Use testing.
DB And the only reason I'm doing this is so that we have some date data.
So we have a people table and they have a birth date, birth time and birth date time.
So let's say I want to select people who have a birth date time between two dates, let's say between
1980.
Let's do January 1st, 1980, and January 1st, 2000.
So I'm going to write it up here.
But the basics would be select name and we'll do birth date time from people where birth date time between.
And if we just do this 2000 or would I say 1980 dash 0101 and 2000.
Dash 0101.
If I just do this on its own, first thing that we're doing here is these are strings, right?
These aren't even dates.
Technically it's text.
But if I do this, it still works.
And that's what I was mentioning.
That's kind of weird is that it works fine enough because my was smart enough.
It figures out, okay, this is a date time.
We're trying to compare it, see if it's between these two things.
What are these?
Well, they're not date times, but we still can handle it.
There's some contingency code written back in my SQL to make that work, but it's much smarter and more
fail proof to do this, cast this as date time, and I'll do this second part on a new line and cast
this as a date time as well, so that we're working with date time, date time, date time.
And you won't see a difference here.
But you can rest easier, can sleep better at night knowing that you're doing the right thing.
We're done.