MySQL BETWEEN .... AND operator
BETWEEN .... AND operator
MySQL BETWEEN AND operator checks whether a value is within a range.
Syntax:
expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise, it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise, type conversion takes place according to the rules
MySQL Version: 5.6
Example: MySQL BETWEEN .... AND operator
The following MySQL statement will fetch the rows from the table publisher which was established between the year 1968 and 1975.
Code:
SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE YEAR(estd) BETWEEN 1968 AND 1975;
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE YEAR(estd) BETWEEN 1968 AND 1975; +-------------------------+-----------+----------+------------+ | pub_name | country | pub_city | estd | +-------------------------+-----------+----------+------------+ | Jex Max Publication | USA | New York | 1969-12-25 | | New Harrold Publication | Australia | Adelaide | 1975-09-05 | | Mountain Publication | USA | Houstan | 1975-01-01 | +-------------------------+-----------+----------+------------+ 3 rows in set (0.01 sec)
Example: BETWEEN - AND operator with MONTH()
The following MySQL statement will fetch the rows from the table publisher which established between the month February and August.
Code:
SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE MONTH(estd) BETWEEN '02' and '08';
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE MONTH(estd) BETWEEN '02' and '08'; +------------------------------+---------+-----------+------------+ | pub_name | country | pub_city | estd | +------------------------------+---------+-----------+------------+ | Ultra Press Inc. | UK | London | 1948-07-10 | | Pieterson Grp. of Publishers | UK | Cambridge | 1950-07-15 | +------------------------------+---------+-----------+------------+ 2 rows in set (0.00 sec)
Example: BETWEEN - AND operator using logical AND
The following MySQL statement will fetch the rows from the table publisher which established between the month May and September and year between 1950 and 1975.
Code:
SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE MONTH(estd) BETWEEN '05' AND '09'
AND YEAR(estd) BETWEEN 1950 AND 1975;
Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE MONTH(estd) BETWEEN '05' AND '09' -> AND YEAR(estd) BETWEEN 1950 AND 1975; +------------------------------+-----------+-----------+------------+ | pub_name | country | pub_city | estd | +------------------------------+-----------+-----------+------------+ | New Harrold Publication | Australia | Adelaide | 1975-09-05 | | Pieterson Grp. of Publishers | UK | Cambridge | 1950-07-15 | +------------------------------+-----------+-----------+------------+ 2 rows in set (0.03 sec)
Example: BETWEEN - AND operator with a date range
The following MySQL statement will fetch the rows from the table publisher which estd between the specified dates.
Code:
SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE estd BETWEEN '1950-01-01' AND '1975-12-31';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: publisher
Sample Output:
mysql> SELECT pub_name,country,pub_city,estd -> FROM publisher -> WHERE estd BETWEEN '1950-01-01' AND '1975-12-31'; +------------------------------+-----------+-----------+------------+ | pub_name | country | pub_city | estd | +------------------------------+-----------+-----------+------------+ | Jex Max Publication | USA | New York | 1969-12-25 | | New Harrold Publication | Australia | Adelaide | 1975-09-05 | | Mountain Publication | USA | Houstan | 1975-01-01 | | Pieterson Grp. of Publishers | UK | Cambridge | 1950-07-15 | +------------------------------+-----------+-----------+------------+ 4 rows in set (0.00 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-between-and-operator-specific-dates- php MySQL examples | w3resource</title>
<meta name="description" content="example-between-and-operator-specific-dates- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of publishers whose month and year of establishment is between 1950-01-01 and 1975-12-31,along with their country, city and date of establishment:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</th><th>Country</th><th>City</th><th>Date of establishment</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE estd BETWEEN "1950-01-01" AND "1975-12-31"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
JSP script:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-between-and-operator-specific-dates</title>
</head>
<body>
<%
try {
Class.forName("com.MySQL.jdbc.Driver").newInstance();
String Host = "jdbc:MySQL://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data = "SELECT pub_name,country,pub_city,estd FROM publisher WHERE estd BETWEEN '1950-01-01' AND '1975-12-31'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publisher</td>
<td>Country</td>
<td>City</td>
<td>Date of establishment</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_name")%></TD>
<TD><%=rs.getString("country")%></TD>
<TD><%=rs.getString("pub_city")%></TD>
<TD><%=rs.getString("estd")%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>
Online Practice Editor:
Slideshow of MySQL Comparison Function and Operators

Previous: Introduction of MySQL functions and operators
Next: COALESCE()
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook