Tutorial

SQL Between, MySQL Between Dates, Not Between

Published on August 3, 2022
Default avatar

By Pankaj

SQL Between, MySQL Between Dates, Not Between

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
Pankaj

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
April 18, 2019

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.

- SAUMYOJIT DAS

    Try DigitalOcean for free

    Click below to sign up and get $200 of credit to try our products over 60 days!

    Sign up

    Join the Tech Talk
    Success! Thank you! Please check your email for further details.

    Please complete your information!

    Get our biweekly newsletter

    Sign up for Infrastructure as a Newsletter.

    Hollie's Hub for Good

    Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

    Become a contributor

    Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

    Welcome to the developer cloud

    DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

    Learn more
    DigitalOcean Cloud Control Panel