SQL BETWEEN operator is used to get values between a range of data inputs. We can use SQL BETWEEN operator with SELECT, UPDATE, INSERT and DELETE statements. Range can be defined based on text, date or numbers values. Here i will show you multiple use cases of this operator like get values between two date ranges using SQL BETWEEN operator, get details between two numbers using SQL Server operator BETWEEN etc.
Get Values between Two Dates using SQL BETWEEN
Let’s say you have a requirement to get all values between two date ranges or specific date time ranges. You can use SQL BETWEEN operator to get this done. Here is the example to get all values between date 19th March 2021 and 20th March 2021. Below example is also an use case of how to use BETWEEN operator in WHERE clause. You can remove ‘-‘ from date ranges if you want and you can directly mention date ranges as ‘20210319’ AND ‘20210320’.
SELECT name, type_desc,create_date, modify_date
FROM sys.objects
WHERE modify_date BETWEEN ‘2021-03-19’ AND ‘2021-03-20’
Here, i have fetched all modified objects on my SQL Server between given date range. Here is the output. We can see there are more than 730 rows returned. You can compare the dates in modify_date column whether output has returned correct data or not.
Now, Next requirement could be to retrieve some data between two date time ranges like you want details between specific time of dates. I will take same above query to show case this example as well. When we don’t specify the time along with date range then it defaults to 12:00 A.M.
SELECT name, type_desc,create_date, modify_date
FROM sys.objects
WHERE modify_date BETWEEN ‘2021-03-19 4:38:00’ AND ‘2021-03-19 4:38:30’
Here, i have retrieved all modified objects between 30 seconds of above given time (Time 4:38:00 to 4:38:30). Here is the output and it is showing only 35 rows.
You can use this SQL between two date ranges or datetime ranges to fetch your data. You can use this operator in INSERT, UPDATE or DELETE statements as well.
Get Values between Two Numbers
This section will explain about getting details between two number ranges. Let me take same table which we have taken for above section sys.objects. Now, i want to get all objects having object id between 25 to 30 or you can mention any range. This is just an example to showcase. These number ranges will change as per your need and nature of data.
SELECT name, object_id, type_desc, create_date
FROM sys.objects
WHERE object_id BETWEEN ’25’ AND ’30’
Note, above object_id is column name and not the T-SQL function. Have a look at its output in below image. We have got only rows which are assigned with these object ids.
Use BETWEEN with NOT Operator
Above all examples shows how to get data between two ranges but we can use NOT operator with BETWEEN to return all values which is not between specified two ranges. Let’s take above example where BETWEEN operator has returned only 5 rows based on their specified two ranges. Now if i will use NOT operator in above T-SQL statement then output will return all rows of table sys.objects except above 4 rows which will be prevented by NOT operator. Let me show you this example.
SELECT name, object_id, type_desc, create_date
FROM sys.objects
WHERE object_id NOT BETWEEN ’25’ AND ’30’
Have a look at below highlighted are where objects ids between 25 to 30 are missing.
Here, i have shown you few use cases of SQL Server T-SQL operator BETWEEN statement. You can go head and try this operator for your business needs. For more information on this operator, visit MSDN article.
Read More
Leave a Comment