SQL Between, MySQL Between Dates, Not Between

Filed Under: SQL

The SQL BETWEEN operator is used along with WHERE clause for providing a range of values. The values can be the numeric value, text value, and date.

SQL BETWEEN operator is almost like SQL IN operators used in a sequential manner.

The values are defined as part of the BETWEEN range are inclusive i.e. the values that are mentioned in the range are included at the start and end values.

Let’s discuss in detail about the BETWEEN operator.

As mentioned above BETWEEN operator can be used along with numeric value, text value, and date. We will discuss all the three in detail below.

SQL Between Syntax


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

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.

SQL BETWEEN operator for Numeric value

We will understand the above-mentioned syntax in more detail through some examples for numeric value.

Let’s consider the following Student table for example purpose.

RollNo StudentName StudentGender StudentAge StudentPercent AdmissionDate
1 George M 14 85 2018-01-01
2 Monica F 12 88 2018-01-31
3 Jessica F 13 84 2018-01-15
4 Tom M 11 78 2017-12-15

I am using MySQL database and here is the script to create and insert example records in the Student table.


CREATE TABLE `Student` (
  `rollno` int(11) unsigned NOT NULL,
  `studentname` varchar(20) DEFAULT NULL,
  `studentgender` varchar(5) DEFAULT NULL,
  `studentage` int(3) DEFAULT NULL,
  `studentpercent` int(3) DEFAULT NULL,
  `admissiondate` date DEFAULT NULL,
  PRIMARY KEY (`rollno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Student` (`rollno`, `studentname`, `studentgender`, `studentage`, `studentpercent`, `admissiondate`)
VALUES
	(1, 'George', 'M', 14, 85, '2018-01-01'),
	(2, 'Monica', 'F', 12, 88, '2018-01-31'),
	(3, 'Jessica', 'F', 13, 84, '2018-01-15'),
	(4, 'Tom', 'M', 11, 78, '2017-12-15');

Scenario: 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 Between Clause example

SQL NOT BETWEEN Operator for Numeric Value

The SQL NOT BETWEEN operator is used for getting the values as part of result set which is outside of the range specified by the BETWEEN operator.

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 not between example

SQL BETWEEN operator for Text value

Scenario: Get the RollNo, StudentName and StudentAge where StudentName is between George and Jessica.


SELECT RollNo, StudentName, StudentAge FROM Student WHERE StudentName BETWEEN 'George' AND 'Jessica';

Output:

RollNo StudentName StudentAge
1 George 14
3 Jessica 13

SQL NOT BETWEEN Operator for Text Value

Scenario: Get the RollNo, StudentName and StudentAge where StudentName is not between George and Jessica.


SELECT RollNo, StudentName, StudentAge FROM Student WHERE StudentName NOT BETWEEN 'George' AND 'Jessica';

Output:

RollNo StudentName StudentAge
2 Monica 12
4 Tom 11

SQL BETWEEN operator for Date value

Scenario: Get the age of students whose admission is between 1st Jan 2018 and 31st Jan 2018.


SELECT StudentAge FROM Student WHERE admissiondate BETWEEN str_to_date('2018-01-01', '%Y-%m-%d') AND '2018-01-31';

Output:

StudentAge
14
12
13

Note that I am using MySQL native function str_to_date to convert string to date. If the string is in default format, we can use it as-is too, just as I have used for the second argument.

If you are using Oracle DB, then corresponding function is TO_DATE.

SQL Between Date Example

SQL NOT BETWEEN Operator for Date Value

Scenario: Get the age of students whose admission is not between 1st Jan 2018 and 31st Jan 2018.


SELECT StudentAge FROM Student WHERE admissiondate NOT BETWEEN str_to_date('2018-01-01', '%Y-%m-%d') AND '2018-01-31';

Output:

StudentAge
11

MULTIPLE BETWEEN operators

We can use multiple between operators too. Its syntax is:


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

Using the above-mentioned syntax, we can use multiple BETWEEN operators.

Scenario: Get the student name with age between 10 and 13 and marks between 80 to 85 percentage.


SELECT StudentName FROM Student WHERE 
StudentAge BETWEEN 10 AND 13 
AND
StudentPercent BETWEEN 80 AND 85;

Output:
SQL Multiple Between Operators

Comments

  1. SAUMYOJIT DAS says:

    SELECT * FROM Orders
    WHERE OrderDate BETWEEN ‘1996-07-01’ AND ‘1996-07-31’;

    SELECT * FROM Orders
    WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

    what is the differnece between them? i am using oracle 11g release 2 (SQL PLUS COMMAND)
    https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_between_date2

    what if i write like this WHERE OrderDate BETWEEN #1996-07-01# AND #1996-07-31#;
    i am not getting what i want . i am getting all the records.
    plzz reply me in email.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages