WEBVTT 00:00.150 --> 00:07.970 So moving right along our next operator will take a look at is called between in between is pretty straightforward. 00:07.980 --> 00:15.000 It allows us to select things based off of two values an upper and lower range so I can say I want to 00:15.000 --> 00:24.810 find you know all people who have a birth date between April 20th of 2017 and April 22nd of 2017 or 00:24.840 --> 00:30.270 I want to find all books you know that have page counts between 100 and 500 pages. 00:30.270 --> 00:34.030 So we actually can do this already using what you know. 00:34.260 --> 00:40.350 So before I show you between how can we accomplish the same thing using the tools that we already have 00:40.710 --> 00:43.230 things that we've learned in this section already. 00:43.290 --> 00:48.930 Because if you take a moment to think about what being between two values actually means it's really 00:48.930 --> 00:52.920 just a combination of greater than and less than. 00:52.970 --> 00:55.770 So how do we combine two different conditions. 00:55.850 --> 01:04.040 Well logical and so let's try an example again just to reiterate if it was unclear we're doing it without 01:04.160 --> 01:05.290 the new between. 01:05.290 --> 01:06.570 Operator we haven't seen that yet. 01:06.590 --> 01:09.520 We're doing it a longer way if you will. 01:09.590 --> 01:14.730 So select all books published between the years 2004 and 2015. 01:15.110 --> 01:19.290 And that includes 2004 and 2015 just to be clear. 01:20.060 --> 01:20.580 OK. 01:20.640 --> 01:27.810 So to do that we use logical and to join together two different conditions two components. 01:27.810 --> 01:34.620 So select title unreleased year blah blah blah that doesn't matter from books where released here is 01:34.620 --> 01:40.590 greater than or equal to 2004 and released there is less than or equal to 2015. 01:40.800 --> 01:47.220 So both parts that have to be true meaning that whatever the book is had selected are all of the books 01:47.220 --> 01:56.160 that are selected are somewhere between 2004 including 2004 and 2015 including 2015. 01:56.970 --> 02:00.450 So I won't make you watch me type this whole thing. 02:00.450 --> 02:01.780 It's going to paste it over here. 02:01.980 --> 02:11.050 But let's start off by just doing this like title and released year from books and just check. 02:11.050 --> 02:12.320 Just we know this. 02:12.330 --> 02:16.130 But just to verify that there are things outside that range. 02:16.470 --> 02:26.670 And now if we paste in using the ampersand ampersand and operator which of course could also be written 02:26.670 --> 02:27.730 this way. 02:28.110 --> 02:35.220 If I could type correctly you'll see that we get things that are between 2004 and 2015 and we know that 02:35.220 --> 02:42.620 it's inclusive as you can see because 2004s here and if we hadn't included that equal sign then it would 02:42.620 --> 02:43.850 not be there. 02:44.430 --> 02:44.770 OK. 02:44.780 --> 02:49.490 So really what I'm trying to show you is that you don't need a fancy between operator to accomplish 02:49.490 --> 02:50.340 this. 02:50.390 --> 02:58.580 You can just do it using good old fashion logic using greater than less then and of logical and however 02:59.360 --> 03:02.870 there is an operator between that will make it easier. 03:03.350 --> 03:04.870 So it looks like this. 03:04.940 --> 03:11.080 The syntax is between and then some value and some other value. 03:11.720 --> 03:13.980 And here just I get it. 03:13.980 --> 03:17.160 It could be confusing when we also have this. 03:17.200 --> 03:20.120 And and this one. 03:20.120 --> 03:21.020 But they are different. 03:21.110 --> 03:24.660 So this and it's paired with between Always. 03:24.680 --> 03:25.750 So here's an example. 03:25.840 --> 03:33.470 Select title and release year again from books where released year between 2004 and 2015. 03:33.500 --> 03:36.510 So this does the exact same thing as this right here. 03:36.710 --> 03:40.010 But we don't have to use a greater than equal to less than equal to. 03:40.040 --> 03:42.090 We don't have to write release twice. 03:42.200 --> 03:47.160 All we have to do is say where release year between 2004 and 2015. 03:47.510 --> 03:50.430 And in my opinion this is much easier to read. 03:50.480 --> 03:55.940 Much easier to remember if you're trying to do this between is a pretty straightforward thing to recall. 03:55.940 --> 03:57.750 It does what it sounds like. 03:57.980 --> 04:06.770 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 04:06.770 --> 04:07.350 it. 04:07.360 --> 04:11.550 So like title release you're from books were released here between 2004 and 2015. 04:11.720 --> 04:16.870 And as you can see we get the same results that we did when we used this version. 04:16.940 --> 04:21.970 It's just a little shorter and cleaner and easier to understand my opinion. 04:22.160 --> 04:23.490 And again it is inclusive. 04:23.510 --> 04:25.620 We have 2004 there. 04:25.820 --> 04:29.960 And one thing to keep in mind is that there may be times where you don't want it to be inclusive and 04:29.960 --> 04:31.930 there's not an easy way just to do that. 04:32.150 --> 04:39.500 Well there is an easy way but it basically just means change this number if you want it to be 2005 to 04:39.530 --> 04:41.910 20 14. 04:41.960 --> 04:49.010 You could do that but there's no easy way to you know do between non-inclusive like here where we can 04:49.010 --> 04:50.770 just eliminate the equal sign. 04:51.290 --> 04:56.460 OK so one other thing is that not between is also a thing. 04:56.810 --> 04:58.800 And it's pretty straightforward. 04:58.820 --> 05:01.880 Basically it does the opposite of between. 05:01.910 --> 05:09.000 So we could say I want all the books that have a page length that is not between 100 and 500. 05:09.050 --> 05:13.750 And that gives us everything less than 100 and everything greater than 500. 05:13.850 --> 05:19.040 So here's our same example if we do select title and released year from books were released there is 05:19.370 --> 05:20.580 not between. 05:20.600 --> 05:29.270 So all that changes as we add and OT before now between 2004 and 2015 it takes that range from 2004 05:29.270 --> 05:30.120 in 2015. 05:30.200 --> 05:32.860 It does everything outside of that. 05:33.140 --> 05:37.070 So I'll just paste it here and then paste it in again. 05:37.410 --> 05:39.560 Now you can see let's do an order by 05:47.780 --> 05:59.400 you can see we have everything up until 2004 and then everything after 2015 but nothing from 2004 up 05:59.400 --> 06:00.560 to 2015. 06:00.660 --> 06:01.850 They're gone. 06:01.920 --> 06:03.530 These are just the leftovers. 06:04.020 --> 06:04.600 OK. 06:04.920 --> 06:08.670 So that's pretty much it for the basics of between and not between. 06:08.700 --> 06:12.800 So before I let you go there is one small side note I just want to bring up. 06:12.870 --> 06:17.910 It's not crucial to understanding between but it's just something that the documentation mentions. 06:17.940 --> 06:22.950 So I figured I should also mention that if you go to the docks or between there is this note at the 06:22.950 --> 06:28.920 end for best results when choosing between with date or time values you're cast to explicitly convert 06:28.920 --> 06:31.390 the values to the desired data type. 06:31.440 --> 06:33.270 So we haven't seen this cast. 06:33.270 --> 06:38.790 I'll talk about it in a moment but what it lets us do is convert from one data type to another from 06:39.310 --> 06:48.090 a date to a Date Time for instance or from a string of our char to a date. 06:48.150 --> 06:50.970 For example if you compare date time to to date values. 06:51.000 --> 06:57.480 So we're trying to say if some date time is between two different dates it's best to convert the date 06:57.480 --> 06:59.650 values to also be date times. 06:59.760 --> 07:05.700 Just you're working with the same kind of consistent data type if you use a string constant such as 07:05.700 --> 07:09.470 2001 Dash 1 dash 1 and in comparison to a date. 07:09.630 --> 07:12.440 It's better to cast the string to a date. 07:12.510 --> 07:15.390 So let's just take a look at how cast works first. 07:15.750 --> 07:16.900 And it's pretty simple. 07:17.040 --> 07:19.230 So let's take something like this. 07:19.260 --> 07:24.080 2000 17 dash 0 5 dash 0 2. 07:24.510 --> 07:27.180 That right there is not technically a date right. 07:27.180 --> 07:30.630 That's not considered a date data type. 07:30.630 --> 07:31.880 It's just text. 07:32.400 --> 07:36.060 Even though it looks like a date and it will it can be treated like one. 07:36.060 --> 07:37.870 My ass is smart enough to figure that out. 07:37.870 --> 07:46.220 If we pass it into a Date function like days or months or format date but it's not actually a date. 07:46.350 --> 07:51.110 But if we wanted to make it an actual date time we can cast it. 07:51.550 --> 07:53.310 And this will work its magic. 07:53.320 --> 07:55.090 Now just casting it like this won't do anything. 07:55.090 --> 07:57.320 I have to say what to cast it as. 07:57.400 --> 08:02.230 So let's cast it as a date time and we won't see anything unless we have to like it here. 08:02.230 --> 08:07.040 So what this will do is it takes the first thing whatever it is and it tries to convert to a date time. 08:07.060 --> 08:11.340 So if there's just you know the number ninety nine or something that will cause a problem. 08:11.650 --> 08:16.960 But in this case this is easily castable to a daytime but you might be wondering OK the date part is 08:16.990 --> 08:17.830 easy to cast. 08:17.830 --> 08:19.290 What about the time part. 08:19.720 --> 08:22.880 Well it just picks 000 0 for you. 08:23.020 --> 08:26.400 So it's now considered a date time. 08:26.440 --> 08:31.780 So basically what the documentation is saying is if you're working with dates and you're trying to compare 08:31.780 --> 08:38.560 them and you're working with between for example it's best to use cast to make sure that all the data 08:38.560 --> 08:40.270 types are the same. 08:40.360 --> 08:43.080 Now it actually this is kind of confusing it will work. 08:43.090 --> 08:48.400 Most of the time just fine if you don't because mice are smart enough to figure out what you want but 08:48.430 --> 08:51.770 it can cause problems so it's just best to do that. 08:51.790 --> 08:59.770 So I'm going to switch over to that testing database I made with this wonderful name used testing D.B 09:00.070 --> 09:03.580 and the only reason I'm doing this is that we have some date data. 09:03.730 --> 09:09.210 So we have a people table and they have a birth date birth time and birth date time. 09:09.340 --> 09:17.050 So let's say I want to select people who have a birth date time between two dates let's say between 09:17.320 --> 09:26.420 1980 to January 1st 1980 and January 1st 2000 so I'm going to write it up here. 09:26.870 --> 09:39.130 But the basics would be select name and we'll do birthdate time from people where birth date time between. 09:39.920 --> 09:43.000 And if we just do this 2000. 09:43.030 --> 09:55.490 Or would I say 1980 dash 0 1 dash 0 1 and 2 0 0 0 0 0 1 0 1. 09:55.490 --> 09:57.850 If I just do this on its own. 09:58.130 --> 10:00.890 First thing that we're doing here is these are strings right. 10:00.890 --> 10:04.480 These aren't even dates technically it's text. 10:04.550 --> 10:07.750 But if I do this it still works. 10:07.750 --> 10:09.410 And that's what I was mentioning. 10:09.430 --> 10:13.150 It's kind of weird is that it works fine enough because my ass was smart enough. 10:13.150 --> 10:15.920 It figures out OK this is the date time or trying to compare it. 10:15.940 --> 10:18.740 See if it's between these two things what are these. 10:18.820 --> 10:22.150 Well they're not daytime's but we still can handle it. 10:22.390 --> 10:26.640 There is some contingency code written back in my ask you help to make that work. 10:26.740 --> 10:30.200 But it's much smarter and more foolproof to do this. 10:30.490 --> 10:41.770 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 10:44.110 --> 10:48.400 so that we're working with date time Date Time Date Time. 10:48.400 --> 10:49.970 And you won't see a difference here. 10:50.980 --> 10:56.010 But you can rest easier can sleep better at night knowing that you're doing the right thing. 10:56.330 --> 10:57.540 OK we're done.