This article will explore how you can select records between a range of two dates using the between operator.
SQL Between Operator
The between operator is very straightforward. You only need to specify the start and end bounds. However, keep in mind that the between operators is inclusive. Therefore, the start and end bounds are included in the resulting set.
We can express the between operator as shown below:
FROM table_name
WHERE column_name BETWEEN start_bound AND end_bound;
SQL Between Two dates
Let us illustrate how we can use the between operator to fetch values between a range of two dates.
Let us create a sample table as shown in the query below:
sample.inventory ( product_name VARCHAR(255) NOT NULL,
price INT NOT NULL,
purchase_date DATE );
The above query creates a table called inventory in the sample database. We then add 3 columns containing the product name, price, and the purchase date.
We can add sample data as shown:
sample.inventory
VALUES
('Smart Wallet', 89, '2022-04-23');
INSERT INTO
sample.inventory
VALUES
('AirTag Tracker', 30, '2022-02-02');
INSERT INTO
sample.inventory
VALUES
('iPad Pro', 999, '2021-12-12');
INSERT INTO
sample.inventory
VALUES
('Flash Drive', 40, '2021-01-01');
Once all the sample data are inserted, it can be verified by running the query below:
This should return all the data as shown:
Example
To learn how to use the between operator with a date range, let us retrieve the records where the purchase date is between 2021-01-01 and 2021-12-31
We can run the query as shown:
*
FROM
sample.inventory
WHERE
purchase_date BETWEEN '2021-01-01'
AND '2021-12-31';
The above query should match the records where the date is within the specified range. This should return two products as shown below:
Conclusion
In this article, we discussed how to retrieve records between a range of two date values using the between operator in SQL.
