Join Adam Wilbert for an in-depth discussion in this video Clarifying ranges with a BETWEEN clause, part of Database Foundations: Creating and Manipulating Data.
- When it comes to numeric fields, we can specify that we're only looking for records that fall within a specified range of values. To do this, we'll use the keywords BETWEEN and AND. Let's start a New Query here, and we'll see how that works. I'm going to type in the SELECT statement, SELECT * FROM ProductListing. Then I want to specify a criteria. I want all of the products where the price is between $65 and $95. Let's go ahead and Execute this query. And we'll see all the products that fall within that specified range. There's another way that we can write this without using BETWEEN and AND, and it would look something like this.
I can specify where the Price is >= 65 AND Price <= 95. And you'll see if I run this, I'll get the exact same results. Now this also works with dates as well. I'm going to select everything from the Invoices table, where the date is between February 8, 2015 and February 14, 2015. I can specify like this. WHERE Date BETWEEN, and then a single quote, I'm going to type in 2015-02-08, closing single quote, AND, opening single quote, 2015-02-14, single quote.
Let's go ahead and Execute this. And we'll see all of the records from the Invoices table that were placed in that specific date range. Now I'm going to comment this line out real quick. I'm just going to click in the beginning and type in two hyphens to turn this into a comment. Now if I Execute it, you'll see that this is actually the internal way the dates are being stored in the database. But if I comment this out, we can actually supply the dates in different formats. So for instance, instead of this order, I can specify it like this. 02-08-2015. And this one here becomes 02-14-2015.
And Execute it. And you'll see that it understands what we're talking about here. We can actually even use text characters in here. So for instance, if I said Feb, and then 08 2015. And over here, type in Feb 14 2015. Go ahead and Execute that. And SQL Server understands that as a date as well. So those are just a couple of ways that we can use the keywords BETWEEN and AND. Using BETWEEN is an easy way to find records that fall in a certain range.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Storing dates, times, and text
- Converting data types
- Creating tables
- Writing T-SQL commands
- Selecting records with queries
- Combining and sorting data
- Creating views
- Creating stored procedures and functions
- Inserting and updating data in a table
- Deleting records and tables