Sql (Not) Between Operator

SQL BETWEEN Operator


The SQL BETWEEN operator is used to fetch or extract values within a given range.

The SQL BETWEEN keyword is a conditional based operator, that meet a condition falling between a specified range of given values.

It can work with any type of data like numbers, text, or dates.



You can also search these topics using sql server between dates, inclusive and exclusive.

SQL BETWEEN Syntax

The below syntax is used to define between operator in SQL WHERE clause:

SELECT column_name1, column_name2 FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Note: value1 and value2 must be the same datatype and It cannot be different.


Sample Database Table - Books

BookId BookName BookPrice RelYear DomainName AuthorName
1 Sql Server Concurrency 136.33 2006 Security Padmavathi
2 Troubleshooting SQL Server 70 2009 Optimization Keshavan
3 The SQL Programming Language 84.22 2008 Optimization Dharan
4 Sql Server Interview Questions 90 2015 Administration Varshini Kutty

SQL BETWEEN Operator Example With Numbers

The following SQL SELECT statement selects the all books with a BookPrice Between "100" AND "200", in the "Books" table:

SELECT * FROM Books WHERE 
BookPrice BETWEEN 100 AND 200;

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
1 Sql Server Concurrency 136.33 2006 Security Padmavathi

It checks for numeric value:

  • Value1:- Greater than or equal
  • Value2:- Less than or equal

SQL NOT BETWEEN Operator Example With Numbers

The SQL BETWEEN condition can use with the SQL NOT operator.

To display the books outside the range of the previous example, use NOT BETWEEN:

SELECT * FROM Books WHERE 
BookPrice NOT BETWEEN 100 AND 200;

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 Troubleshooting SQL Server 70 2009 Optimization Keshavan
3 The SQL Programming Language 84.22 2008 Optimization Dharan
4 Sql Server Interview Questions 90 2015 Administration Varshini Kutty

SQL BETWEEN Operator Example With Text Values

The following SQL statement selects all books with a AuthorName beginning with any of the letter BETWEEN 'E' and 'P', in the "Books" table:

SELECT * FROM Books WHERE 
AuthorName BETWEEN 'E' AND 'P';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 Troubleshooting SQL Server 70 2009 Optimization Keshavan

It checks for text value:

  • Value1:- Greater than or equal
  • Value2:- Less than only

SQL NOT BETWEEN Operator Example With Text Values

To display the books outside the range of the previous example, use NOT BETWEEN:

SELECT * FROM Books WHERE 
AuthorName NOT BETWEEN 'E' AND 'V';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
3 The SQL Programming Language 84.22 2008 Optimization Dharan
4 Sql Server Interview Questions 90 2015 Administration Varshini Kutty


You can also search these topics using ms sql date between, retrieve data between two dates, between vs greater than performance, sql query in vs between.

SQL Between and NOT Between

SQL BETWEEN

In SQL, the BETWEEN...AND operator is used to pick in-between values from a given range of data. In SELECT, UPDATE, and DELETE statements/queries, it's utilised in the WHERE clause.

The BETWEEN operator accepts both beginning and ending values.

The BETWEEN operator can be used with numeric, text, and date values.

Syntax for SQL BETWEEN…AND operator

SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 BETWEEN value1 AND value2;

Let's put these statements into real use.

We've a table named Students in our database that contains the following records:

ROLL_NO NAME SUBJECT
1 Will Java
2 Sam Phython
3 Sara HTML
4 Rim Java
5 Micheal SQL
6 Lara Java

Now lets select values in a certain range from Students table.

Example 1: BETWEEN

SELECT * FROM Students
WHERE ROLL_NO BETWEEN 2 AND 5;

The above SQL statement selects all students that their ROLL_NO is BETWEEN 2 and 5.

Output:

ROLL_NO NAME SUBJECT
2 Sam Phython
3 Sara HTML
4 Rim Java
5 Micheal SQL

Now lets select values in a outside the range from Students table.

Example 2: Using the above-mentioned syntax, we can define values as part of BETWEEN operator. Also, the syntax mentioned above remains the same for usage with a numeric value, text value, and date value.

Get the percentage of students whose age is between 11 and 13.

SELECT StudentPercent FROM Student WHERE StudentAge BETWEEN 11 AND 13;

Output:

StudentPercent
88
84
78

SQL NOT BETWEEN

The SQL NOT BETWEEN operator is used to extract values from a result set that are outside of the BETWEEN operator's defined range.

Syntax for SQL NOT BETWEEN…AND operator

SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 NOT BETWEEN value1 AND value2;

Example 1: HOW TO USE NOT BETWEEN…AND IN WHERE CLAUSE IN SELECT QUERIES

SELECT Student_ID, Student_name, City, Age from student
WHERE Age NOT BETWEEN 19 AND 25;

Example 2: NOT BETWEEN

SELECT * FROM Students
WHERE ROLL_NO NOT BETWEEN 2 AND 5;

The above SQL statement selects all students that their ROLL_NO is NOT BETWEEN 2 and 5.

Output:

ROLL_NO NAME SUBJECT
1 Will Java
6 Lara Java

Example 3: Scenario: Get the percentage of students whose age is not between 11 and 13:

SELECT StudentPercent FROM Student WHERE StudentAge NOT BETWEEN 11 AND 13;

Output:

StudentPercent
85

SQL Between Cast Dates

Use the CAST() function to explicitly convert date or time values to the suitable data type when employing the BETWEEN operator for the best results.

Example 1:

SELECT *
FROM orders
WHERE order_date BETWEEN CAST('2016/04/19' AS DATE) AND CAST('2016/05/01' AS DATE);

There will be 3 records selected. These are the results that you should see:

Order_id Customer_id Order_date
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

This example would return all records from the orders table where the order_date is between Apr 19, 2016 and May 1, 2016 (inclusive).

Example 2: For example, if you use a string such as '2016-12-31' in a comparison to a DATE, cast the string to a DATE, as follow:

The following SQL statement selects all the employees who hired between 1st January 2006 (i.e. '2006-01-01') and 31st December 2016 (i.e. '2016-12-31'):

SELECT * FROM employees WHERE hire_date
BETWEEN CAST('2006-01-01' AS DATE) AND CAST('2016-12-31' AS DATE);

Result:

Emp_id Emp_name Hire_date Salary Dept_id
4 Rick Deckard 2007-01-03 7200 3
5 Martin Blank 2008-06-24 5600 NULL

SQL Between Dates

In SQL, dates can be complicated, and how you utilise the BETWEEN condition with dates is dependent on the database you're working with.

Example 1: In such situations, when you have both upper and lower limits for your range, you can choose to use a single condition that uses the BETWEEN operator instead of using two different conditions, as in:

SELECT *
FROM Employees
WHERE HireDate BETWEEN '2012-01-01' AND '2014-12-31';

Output:

ID Name Age Job Salary HireDate
2 Kim 26 Manager 55000 2014-04-25
3 Eve 22 Developer 32000 2013-03-11
6 Sam 30 Janitor 10000 2012-02-10

Example 2: In this example, we have a table called orders with the following data:

Order_id Customer_id Order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

The Query will be look like this,

SELECT *
FROM orders
WHERE order_date BETWEEN '2016/04/19' AND '2016/05/01';

Example 3: show the date between two dates using an example.

SELECT name,start_date FROM employee
WHERE start_date BETWEEN '2000-01-01' AND '2006-01-01';

In the above output, we have shown the number of employees who started job between 2000-01-01 and 2006-01-01, as a result, we found that there are 9 employees.

Example 4: Collect records of Feb to Aug for the years 2004 and 2005. To get the records between two months with the year we have to change this query. Let us say we want records from March 2015 to Feb 2016.

SELECT FROM table_name WHERE dte_field 
BETWEEN '2015-03-01' AND LAST_DAY('2016-02-01')

Example 5: Two dates between single quotes like:

select Date, TotalAllowance from Calculation where EmployeeId = 1
and Date between '2011/02/25' and '2011/02/27'

SQL Between Dates with Time

Use the BETWEEN keyword to separate the two dates and times specified in the query. The WHERE keyword comes before this to satisfy the requirement created by the BETWEEN clause. As previously indicated, the date and time format in our table will be yyyy:mm: dd hh:mm: ss, as specified by DATETIME2. The time is formatted as a 24-hour clock.

Example 1: Retrieve the details of the transactions done between 10:00 am, 1st February 2001 and 10:00 pm, 1st May 2007.

SELECT * FROM ATM WHERE 
TRANSACTION_TIME BETWEEN
'2001-02-01 10:00:00' AND
 '2007-03-01 22:00:00';

Example 2: Retrieve the details of the transactions done between 09:00 pm, 28th February 2005 and 12:00 am, 25th December 2008.

SELECT * FROM ATM WHERE TRANSACTION_TIME
BETWEEN '2005-02-28 21:00:00' 
AND '2008-12-25 00:00:00';

Note:– The 5th tuple is not displayed as its transaction time is 00:01:00 whereas our query has limited the time to 00:00:00.

Example 3: “sql query between two dates and times”:

SELECT * FROM `objects` 
WHERE  (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
WHERE 
requireddate BETWEEN 
CAST('2003-01-01' AS DATE) AND 
CAST('2003-01-31' AS DATE);

Example 4: You can use the BETWEEN operator to select employees who were born between 01-Jan-1948 and 01-Jan-1960 as follows:

SELECT lastname, 
firstname, birthdate
FROM employees
WHERE
birthdate BETWEEN '1948-01-01' AND '1960-01-01';

In case the column that you want to compare is a DATETIME column, the following expression:

dt BE\ETWEEN '1980-01-01' AND '1980-01-02';

is translated as:

dt BETWEEN '1980-01-01 00:00:00.000000 AND '1980-01-02 00:00:00.000000';

Because the time part is not specified in the date literals, the database engine uses 12:00:00 AM as the default time. It means any row that contains a time part after 12:00 A.M. on 1980-01-01 is not returned because it is outside the range.


SQL Between Float

use BETWEEN in the entry_key column it includes the all entry_keys. When I search BETWEEN 2 and 2.21 it will find the entry with 2.2.

And, when I try in the mysql shell the following:

SELECT 2.2 BETWEEN 2 and 2.2;

Output:

2.2 BETWEEN 2 and 2.2
1

You should use a decimal data type rather than a float. Equality, and hence between, for floating point values is imprecise


SQL Between for Text

Example 1: 'Between...and' operator for text value:

BEGIN
IF 'C' BETWEEN 'A' AND 'D' THEN
  DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
  DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END;

PL/SQL procedure successfully completed.

Example 2: example for the expression:

where food_name BETWEEN 'G' AND 'O'

will match 'O', but not any other string beginning with 'O'.

Once simple kludge is to use "~". This has the largest 7-bit ASCII value, so for English-language applications, it usually works well:

where food_name between 'G' and 'O~'

You can also do various other things. Here are two ideas:

where left(food_name, 1) between 'G' and 'O'
where food_name >= 'G' and food_name < 'P'

The important point, though, is that between works the same way regardless of data type.


SQL Between in Case Statement

The WHEN clause, not the THEN clause, should include the Boolean statement (which returns the value).

Example 1: sample SQL statement using a CASE expression on the title table in the sample pubs database:

SELECT title, price,
  Budget = CASE price
  WHEN price > 20.00 THEN 'Expensive'
  WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
  WHEN price < 10.00 THEN 'Inexpensive'
  ELSE 'Unknown'
  END,
FROM titles

This statement would return results similar to these:

Title Price Budget
Cooking with Computers 11.95 Moderate
Straight Talk About Co 19.99 Moderate
The Busy Executive's D 19.99 Moderate
You Can Combat Compute 2.99 Inexpensive
Silicon Valley Gastron 19.99 Moderate
The Gourmet Microwave 2.99 Inexpensive
But Is It User Friendl 22.95 Expensive
Secrets of Silicon Val 20.00 Moderate
Net Etiquette (null) Unknown

Example 2: While grouping in a rang it is better to use MySQL BETWEEN Query

SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `gender` , 
CASE 
WHEN mark BETWEEN  90  AND 100 THEN   'A'
WHEN mark BETWEEN  80  AND 89 THEN    'B'
WHEN mark BETWEEN  70  AND 79 THEN   'C'
ELSE 'FAIL'
END AS  grade
FROM  `student`

Example 3: Take out the MONTHS from your case, and remove the brackets... like this:

CASE 
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'

You can think of this as being equivalent to:

CASE TRUE
 WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
 ELSE 'NOTHING'
END AS 'MONTHS'

SQL Between Inclusive and Exclusive

The BETWEEN operator is used to limit the results to a specific range. Numbers, messages, and dates can all be used as values.

The BETWEEN Operator in SQL The BETWEEN operator accepts both beginning and ending values.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SQL Between Min and Max

The BETWEEN operator can be used to run a query based on a condition like this. NOT BETWEEN min AND max is the same as NOT BETWEEN min AND max (expr BETWEEN min AND max).

BETWEEN gives 1 if expr is larger than or equal to min and less than or equal to max.

If not, it returns 0.

This is the same as the equation (min <= expr AND expr <= max).

Syntax:

SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE column_name BETWEEN min_value AND max_value;

Example 1:

SELECT 1 BETWEEN 2 AND 3;

Output:

1 BETWEEN 2 AND 3
0

Example 2: The BETWEEN operator tests if a value is within a specified range. It uses the syntax value BETWEEN min AND max:

SELECT 3 BETWEEN 2 AND 6;

The statement shown above is equivalent to the following statement:

SELECT 3 >= 2 AND 3 <= 6;

Example 3: The following SQL statement will return only those employees from the employees table, whose salary falls within the range of 7000 and 9000.

SELECT * FROM employees 
WHERE salary BETWEEN 7000 AND 9000;

SQL Between Multiple Range

Example 1: BETWEEN is a nice method to help define ranges.

SELECT boroid
FROM districts
WHERE boroid BETWEEN 101 AND 108
  OR boroid BETWEEN 301 AND 303
  OR boroid = 402;

Example 2: multiple ranges within the between:

select id, price
from tablename
where price between 500 and 1500
or price between 2500 and 4500

Example 3: I have a table like the one below…

ID Price
1 1000
2 2000
3 4000
4 5000

Now I need a query to select values between 500 and 1500 And between 2500 and 4500. i.e. like using two between in a single select query :

select id, price
from tablename
where price between 500 and 1500
or price between 2500 and 4500

Output:

ID Price
1 1000
3 4000

SQL Between Now and 7 days

Example: MySql GETDATE() for now and 7 days:

select * from tab
where DateCol between adddate(now(),-7) and now() 

SQL Between String

You can create conditions that search for ranges of strings by defining a character range, dates, and numerals.

Example 1: The following SQL statement selects all the employees whose name beginning with any of the letter between 'O' and 'Z':

SELECT * FROM employees
WHERE emp_name BETWEEN 'O' AND 'Z';

Output:

Emp_id Emp_name Hire_date Salary Dept_id
2 Tony Montana 2002-07-15 6500 1
3 Sarah Connor 2005-10-18 8000 5
4 Rick Deckard 2007-01-03 7200 3

Example 2: Query will find all the existing Customers in the Customers table whose Last Name is in the middle of Carlson and Ruiz

SELECT [FirstName]
 ,[LastName]
 ,[YearlyIncome]
 ,[Education]
 ,[Occupation]
FROM [Customer]
WHERE [LastName] BETWEEN 'Carlson' AND 'Ruiz'
ORDER BY [LastName]

Example 3: When you are searching for employees whose names are between the letters ‘A’ and ‘F’. Here’s what the query would look like:

SELECT *
FROM Employees
WHERE Name BETWEEN 'A' AND 'F';

Output:

ID Name Age Job Salary HireDate
1 Bob 28 Manager 60000 2011-03-07
3 Eve 22 Developer 32000 2013-03-11

Example 4: Suppose you want to search for last names that begin with the letter F. The following clause won’t work because it will retrieve someone whose last name is the letter G (is the letter G, not starts with the letter G):

WHERE last_name BETWEEN 'F' AND 'G'

This next clause shows the correct way to specify the ending point (in most cases):

WHERE last_name BETWEEN 'F' AND 'Fz'

SQL Between Time Range

Using the BETWEEN clause, retrieve data between two date time periods, such as details between specified timestamps.

Example 1: 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’

Example 2: In this section, we will use the table named journey, and a description of the table is given below.

Let’s see the arrival and departure of the person using the below code.

SELECT * FROM journey 
WHERE arrival 
BETWEEN '2021-05-06 07:30:00' AND '2021-05-07 20:40:30';

In the above code, checking the arrival or departure of a person between the date timestamp range of ‘2017-05-06 07:30:00’ and ‘2019-07-14 20:40:30’.


SQL Between Values

The SQL BETWEEN condition makes it simple to see if an expression falls inside a specified range of values (inclusive).

The BETWEEN operator is used in the WHERE conditions to filter records that fall within a certain range. Strings, integers, and dates are examples of possible values. With the AND operator, the range of values must be provided.

Syntax:

SELECT column1, column2,..
FROM table
WHERE column BETWEEN begin_value AND end_value

If the expression is higher than or equal to the low value (>=) and less than or equal to the high value (=), the BETWEEN operator returns true.

Example 1: The following query selects product whose unit price is from $18 to $19:

SELECT productName, unitPrice
FROM
    products
WHERE
    unitPrice BETWEEN 18 AND 19;

Example 2: The following statement uses the BETWEEN operator to find all employees whose salaries are between 2,500 and 2,900:

SELECT employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    salary BETWEEN 2500 AND 2900
ORDER BY 
	salary DESC;

Example 3: use the following Employee tables in all examples.

EmpId FirstName LastName Email Salary HireDate
1 John King john.king@abc.com 33000 2018-07-25
2 James Bond 2018-07-29
3 Neena Kochhar neena@test.com 17000 2018-07-22
4 Lex De Haan lex@test.com 15000 2018-09-08
5 Amit Patel 18000 2019-01-25
6 Abdul Kalam abdul@test.com 25000 2020-07-14

Consider the following query.

SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE Salary BETWEEN 10000 AND 20000;

Above, the Salary column is used with the BETWEEN operator to filter records. The Salary BETWEEN 10000 AND 20000; specifies that the values in the Salary column should be between 10000 and 20000 (inclusive of both values).

Output:

EmpId FirstName LastName Salary
3 Neena Kochhar 17000
4 Lex De Haan 15000
5 Amit Patel 18000

The AND operator must be used with the BETWEEN operator; otherwise, an error will occur.


SQL Between vs Greater Than

They're the same: BETWEEN is a shortcut for the question's longer phrasing, which contains both values (EventDate >= '10/15/2009' and EventDate = '10/19/2009').

The syntax of the BETWEEN operator is as follows:

expression Where condition >= AND condition <=;

The BETWEEN operator returns true if the expression is greater than or equal to ( >=) the low value and less than or equal to ( <=) the high value.

Between Query 1:

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'

Greater than Query 2:

SELECT EventId, EventName
FROM EventMaster
WHERE EventDate >='10/15/2009'
AND EventDate <='10/18/2009'

SQL Between Yesterday and Today

Example 1: To combine the variable date and specific time values.

With all the numerous local formats, date and time constants can be capricious. We can prevent this with TimeSerial:

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] Between Date()-1 + TimeSerial(18,0,0) And Date() + TimeSerial(18,0,0)

(First suggestion, works with German date format)

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] Between Date()-1 + #18:00:00# And Date() + #18:00:00#

Between Date()-1 + #12/30/1899 18:00:00# And Date() + #12/30/1899 18:00:00# 1899-12-30 is "Date zero" in Access.

Example 2: You can always find today (with no time):

SELECT CONVERT(date, GETDATE());

So to find all of the data for yesterday, you say:

DECLARE @today date = GETDATE();
SELECT ...
WHERE createDate >= DATEADD(DAY, -1, @today)
  AND createDate <  @today;

For today, it's a simple change:

WHERE createDate >= @today
AND createDate <  DATEADD(DAY, 1, @today);

Example 3: Between Yesterday at 23:00:00 and today 06:59:59:

We are using with a datetime stamp. The below works but it is also including records at 7:00:00 which i understand why.

How would i exclude 7:00:00 and go records below that time e.g. <= 06:59:59.

CREATE_DATE Between DATEADD(HOUR,23,DATEADD(day,DATEDIFF(day,0,GETDATE()-1),-0)) 
And DATEADD(HOUR,7,DATEADD(day,DATEDIFF(day,0,GETDATE()),-0)))

SQL Between Multiple

SQL provides a number of predicates, each of which uses a different operator to analyse rows, allowing users to retrieve granular result sets. There are two sorts of predicates in this manual: range predicates that use the BETWEEN operator and set membership predicates that use the IN operator.

Use the BETWEEN operator in SQL to see if the values in a column are within a certain range.

Syntax:

SQL Multiple Between Syntax

SELECT Column(s) FROM table_name WHERE column_name 
BETWEEN value1 AND value2 AND column_name 
BETWEEN value3 and value4 ... AND column_name 
BETWEEN valueN and valueM

Example 1: Results Multiple between A and B, or it is between C and D etc.

SELECT Description, SUM(Balance) AS Total_Balance
FROM Chart_Of_Accounts
WHERE (Account BETWEEN '400401' AND '400407') 
OR (Account BETWEEN '440094' AND '440100') 
OR (Account BETWEEN '450094' AND '450100')
GROUP BY Description

Example 2: SQL Multiple Between Examples

Use SQL multiple Between operator to fetch student records who have given exam of semester 1 to 2 and achieved percentage more than 70 and less than 90.

SELECT tblstudent.studentname, tblresult.examdate AS 'Exam Date', 
tblresult.examname, tblresult.obtainmark, tblresult.pecentage, tblresult.subjectid
FROM tblresult LEFT OUTER JOIN tblstudent ON tblstudent.student_id = tblresult.student_id
WHERE  (tblresult.pecentage BETWEEN 70 AND 90) AND (tblresult.examname BETWEEN 'sem1' AND 'sem2');

In above query two SQL Between operator is used to compare two range of values with two different columns percentage and examname.

The resulting records of this query contains student details who has got percentage between 70 to 90, and with examname of sem1 to sem2.