We extract data from SQL Server tables along with various conditions. Usually, we have data in large amounts and SQL Between operator helps to extract a specific range of data from this huge data. For example, suppose we want to know the product sales between Jan 2019 to May 2019. In this case, we can use this operator in a SQL query.
In this article, we will explore the SQL Between operator and its usage scenarios.
The Syntax of SQL Between operator
We use SQL Between operator in the Where clause for selecting a range of values. The syntax for SQL Between is as follows
|
1 2 3 4 5 6 7 |
SELECT Column_name FROM table WHERE test_expression BETWEEN min_value(expression) AND max_value; |
- Test_Expression: It is the expression or column on which we need to define a range
- Min_value(expression): We define a minimum range for the between operator. Its data type should be the same as of test_expression
- Max_value)expression): It is the maximum range for the between operator. It should also be the same data type as of min_value(expression) and test_expression
In my example, product sales between Jan 2019 to May 2019, we have min_value as Jan 2019 and max_value as May 2019.
The SQL Between operator returns TRUE if the Test_expression value is greater than or equal to the value of min_value(expression) and less than or equal to the value of max_value ( expression).
If the condition is not satisfied, it returns FALSE.
Usually, developers confuse on whether the result will be inclusive or exclusive of the specified range. For example, does the result set include (in product sales between Jan 2019 to May 2019) both Jan 2019 and May 2019 sales data and exclude Jan 2019 and May 2019. We will look at this with the next section of examples.
Let’s prepare a sample table and insert data into it.
I am using ApexSQL Generate as shown in the following screenshot. You can specify a custom date range to select appropriate data.
Once we generate the date, you can check the sample data in the table.
Example 1: SQL Between operator with Numeric values
We can specify numeric values in the SQL Between operator. Suppose we want to get data from Productdata table for ProductID between 101 and 105.
Execute the following query to get the data. You can specify numeric value directly in between operator without any single quote.
|
1 2 3 |
SELECT * FROM productdata WHERE ProductID BETWEEN 101 AND 105; |
Previously, we thought of a question – whether the between operator contains inclusive or exclusive values. In the output, we can see that both ProductID 101 and 105 are included in the output of SQL Between.
Example 2: SQL Between operator with Date Range
We can use SQL Between operator to get data for a specific date range. For example, in the following query, we want to get data from ProductSaleDate table in the range of 1st Jan 2019 and 1st April 2019. You need to specify the dates in a single quote.
|
1 2 3 4 |
SELECT * FROM productdata WHERE ProductSaleDate BETWEEN '2019-01-01' AND '2019-04-01' ORDER BY ProductSaleDate; |
We can use the CAST function to convert the value in the desired date format explicitly. For example, in the following query, we use the CAST function to convert a string into a date data type.
|
1 2 3 4 |
SELECT * FROM productdata WHERE ProductSaleDate BETWEEN CAST('20190101' as date) AND CAST('20190401' as date) ORDER BY ProductSaleDate; |
Similarly, we can use the CAST function to convert a string into a datetime2 data type.
|
1 2 3 4 |
SELECT * FROM productdata WHERE ProductSaleDate BETWEEN CAST('20190101' as datetime2) AND CAST('20190401' as datetime2) ORDER BY ProductSaleDate; |
In our sample data, productsaledate contains date along with the timestamp. In the SQL between the operator, we didn’t specify timestamp to get the required data. If you do not specify any timestamp, SQL automatically specifies timestamp as midnight. We need to b careful in using SQL Between with date columns. Let’s explore these using a further example.
Let’s create a table variable and insert few records in it. In the insert statement, you can see we are using timestamp for a few users while few users have the only date.
|
1 2 3 4 5 6 7 |
DECLARE @Users TABLE(Name varchar(40), ModifyDate DateTime) INSERT INTO @Users (Name, ModifyDate) VALUES ('Raj', '2019-02-01'); INSERT INTO @Users (Name, ModifyDate) VALUES ('Raju', '2019-02-01 09:32:42'); INSERT INTO @Users (Name, ModifyDate) VALUES ('Rajendra', '2019-02-01 15:04:09'); INSERT INTO @Users (Name, ModifyDate) VALUES ('Akshita', '2019-02-03'); INSERT INTO @Users (Name, ModifyDate) VALUES ('Kashish', '2019-02-03 21:42:43'); |
In the records, you can see SQL Server added timestamp automatically in case we do not specify timestamp explicitly.
This scenario is also applicable once we retrieve the records using SQL Between operator.
Let’s select the data from the table variable using the following query.
|
1 2 3 |
select * from @Users WHERE ModifyDate BETWEEN '2019-02-01' AND '2019-02-03' ORDER BY ModifyDate |
We should get all the records in the table but let’s view the output.
In the output, we do not have a record for user Kashish. As stated earlier, if we do not specify timestamp in the SQL Between operator, SQL Server automatically uses timestamp until midnight. Due to this reason, we get the output until 2019-02-03 00:00:00.000.
For User Kashish modifieddate is 2019-02-03 21:42:43 and it does not fulfill condition in SQL Between operator. Due to this, we do not get this user in the output.
Let’s execute the command again with a timestamp in SQL Between operator.
|
1 2 3 |
select * from @Users WHERE ModifyDate BETWEEN '2019-02-01' AND '2019-02-03 22:00:00' ORDER BY ModifyDate |
We get all records this time because it checks for data until 2019-02-03 22:00:000.
Example 3: SQL Between operator with a string
In this example, we want to get data from Productdata table having ProductCode between A and D. We also need to use a single quote for a string to get inclusive data.
|
1 2 3 4 |
SELECT * FROM productdata WHERE ProductCode BETWEEN 'A' AND 'C' ORDER BY ProductCode; |
In the output, you can see data for productcode between A and C.
Example 4: SQL NOT Between operator with a string
We might also want to exclude a particular range of data in the output. In this case, we can use SQL Between operator with Not statement.
Suppose, we want to get the top 10 records from the ProductData table but do not want to include ProductID 104 and 108. We can specify this condition using Not Between operator.
|
1 2 3 4 |
SELECT top 10 * FROM productdata WHERE ProductID Not BETWEEN 104 and 108 ORDER BY ProductID; |
In the output, we do not have data from ProductID 104 and 108.
Conclusion
In this article, we explored SQL Between operator along with its use cases. We should be familiar with the function to get a specific range of data. If you have any comments or questions, feel free to leave them in the comments below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023