So moving right along our next operator will take a look at is called between in 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 you know all people who have a birth date between April 20th of 2017 and April 22nd of 2017 or
I want to find all books you know that have page counts between 100 and 500 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've 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 it was unclear we're doing it without
the new between.
Operator we haven't seen that yet.
We're doing it a 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.
OK.
So to do that we use logical and to join together two different conditions two components.
So select title unreleased year blah blah blah that doesn't matter from books where released here is
greater than or equal to 2004 and released there is less than or equal to 2015.
So both parts that have to be true meaning that whatever the book is had selected are all of the books
that are selected are somewhere between 2004 including 2004 and 2015 including 2015.
So I won't make you watch me type this whole thing.
It's going to paste it over here.
But let's start off by just doing this like title and released 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's inclusive as you can see because 2004s here and if we hadn't included that equal sign then it would
not be there.
OK.
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 fashion logic using greater than less then and of logical and 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.
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 and it's paired with between Always.
So here's an example.
Select title and release year again from books where released year 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 twice.
All we have to do is say where release year 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 is a pretty straightforward thing to recall.
It does what it sounds like.
So let's try to just copy it and bring it here so we have a record in the notes and then I'll just paste
it.
So like title release you're from books were released here between 2004 and 2015.
And as you can see we get the same results that we did when we used this version.
It's just a little shorter and cleaner and easier to understand 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 it basically just means change this number if you want it to be 2005 to
20 14.
You could do that but there's no easy way to you know do between non-inclusive like here where we can
just eliminate the equal sign.
OK 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 100 and 500.
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 released year from books were released there is
not between.
So all that changes as we add and OT before now between 2004 and 2015 it takes that range from 2004
in 2015.
It does everything outside of that.
So I'll just paste it here and then paste it in again.
Now you can see let's do an order by
you can see we have everything up until 2004 and then everything after 2015 but nothing from 2004 up
to 2015.
They're gone.
These are just the leftovers.
OK.
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 that if you go to the docks or between there is this note at the
end for best results when choosing between with date or time values you're cast to explicitly convert
the values to the desired data type.
So we haven't seen this cast.
I'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 you're working with the same kind of consistent data type if you use a string constant such as
2001 Dash 1 dash 1 and in 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.
2000 17 dash 0 5 dash 0 2.
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 ass 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 to like it here.
So what this will do is it takes the first thing whatever it is and it tries to convert to a date time.
So if there's just you know the number ninety nine or something that will cause a problem.
But in this case this is easily castable to a daytime but you might be wondering OK the date part is
easy to cast.
What about the time part.
Well it just picks 000 0 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 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 mice are 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 used testing D.B
and the only reason I'm doing this is 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 to 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 birthdate time from people where birth date time between.
And if we just do this 2000.
Or would I say 1980 dash 0 1 dash 0 1 and 2 0 0 0 0 0 1 0 1.
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.
It's kind of weird is that it works fine enough because my ass was smart enough.
It figures out OK this is the date time or trying to compare it.
See if it's between these two things what are these.
Well they're not daytime's but we still can handle it.
There is some contingency code written back in my ask you help to make that work.
But it's much smarter and more foolproof 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.
OK we're done.