Home » Core Java » sql » SQL Between, MySQL Between Dates, Not Between

About Odysseas Mourtzoukos

Mourtzoukos Odysseas is studying to become a software engineer, at Harokopio University of Athens. Along with his studies, he is getting involved with different projects on gaming development and web applications. He is looking forward to sharing his knowledge and experience with the world.

SQL Between, MySQL Between Dates, Not Between

In this example, we will see how to use the BETWEEN operator included in the SQL.

1. Introduction

The BETWEEN is a logical operator, often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements, and gives us the ability to specify whether a value is in a given range or not.

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

2. SQL BETWEEN Example

In this example, we will see the basic usage of BETWEEN operator. We will create a TABLE and we will insert some ROWS. Then we will execute some SELECT queries and see the results.

BasicExample.sql
create table employee (id INT, name VARCHAR(50), date_of_birth DATE);
 
insert into employee (id, name, date_of_birth) values (1, 'John', '2020-01-19 04:41:02');
insert into employee (id, name, date_of_birth) values (2, 'Mary', '2020-03-05 01:23:00');
insert into employee (id, name, date_of_birth) values (3, 'George', '2001-12-26 05:01:00');
insert into employee (id, name, date_of_birth) values (4, 'Dany', '1996-08-04 00:00:00');
insert into employee (id, name, date_of_birth) values (5, 'Bob', '1956-01-30 00:00:00');

SELECT * FROM employee WHERE id BETWEEN 2 AND 4;
SQL Between - BasicExample Output
Fig. 1: BasicExample Output

As we can see, this query returned the rows with id between 2 and 4 ( 2 and 4 also included).

3. MYSQL BETWEEN Example with Dates

When we use the BETWEEN operator with date values, we have to CAST the type of column or expression to the DATE type.

BasicExample.sql
SELECT * FROM employee WHERE date_of_birth BETWEEN CAST('1992-00-00' AS DATE) AND CAST('2003-01-01' AS DATE);

SQL Between - BETWEEN with Date Output

Fig. 2: BETWEEN with Date Output

4. NOT BETWEEN

Also, we can combine BETWEEN and NOT operators to get all the rows that are not between the given range.

BasicExample.sql

SELECT * FROM employee WHERE id NOT BETWEEN 2 AND 4;
SELECT * FROM employee WHERE date_of_birth NOT BETWEEN CAST('1992-00-00' AS DATE) AND CAST('2003-01-01' AS DATE);
SQL Between - Same Examples With NOT Operator Output

Fig. 3: Same Examples With NOT Operator Output

5. Summary

In these examples, we learned how we can use the BETWEEN operator to our advantage on simple data types or DATE type data.

7. Download the source code

This was an SQL Between, MySQL Between Dates, Not Between Example.

Download
You can download the full source code of this example here: SQL Between, MySQL Between Dates, Not Between

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments