Rajendra Gupta
insert sample data

SQL Between Operator overview and examples

June 11, 2019 by

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

  • 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.

ApexSQL Generate

Once we generate the date, you can check the sample data in the table.

sample data

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.

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.

 Between operator with Numeric Range

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.


SQL Between operator with Date Range

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.

Similarly, we can use the CAST function to convert a string into a datetime2 data type.

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.

In the records, you can see SQL Server added timestamp automatically in case we do not specify timestamp explicitly.

insert sample data

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.

We should get all the records in the table but let’s view the output.

date range with timestamp

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.

We get all records this time because it checks for data until 2019-02-03 22:00:000.

date range

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.

In the output, you can see data for productcode between A and C.

SQL Between operator with a string

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.

In the output, we do not have data from ProductID 104 and 108.

SQL NOT Between operator with a string

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

441 Views