How to select dates between the two given dates in SQL
- How-Tos FAQs
- December 17, 2018
We can select dates between the two given dates in SQL using the below methods.
- BETWEEN … AND … Operator
- >= (Greater Than or Equal to) and <=(Smaller Than or Equal to) Operator
Both of the above operators are inclusive. It means it includes both start and end values in the SELECT query. However, the second method is more flexible. We can include or exclude the edge values by including or excluding the “Equal to’ (=) sign.
Let us consider the below table:
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
INSERT INTO orders
SELECT 1,'Rose', '2020-01-08', 19 UNION ALL
SELECT 2,'Jack', '2020-02-03', 4 UNION ALL
SELECT 3,'Jack', '2020-03-03 03:04:00', 40 UNION ALL
SELECT 4,'Rose', '2020-04-18', 47 ;
-- Using BETWEEN... AND... operator
SELECT *
FROM orders
WHERE order_date BETWEEN '2020-01-01' AND '2020-02-03';
-- Using >= (Greater Than or Equal to) and <=(Smaller Than or Equal to) Operator
SELECT *
FROM orders
WHERE order_date >= '2020-01-01' AND
order_date <= '2020-02-03';
-- Output
# order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Rose 2020-01-08 00:00:00 19
2. Jack 2020-02-03 00:00:00 4We must remember that when the datatype of a column is DATETIME and if we don’t pass the time part in the date provided, it will assume the time part as 00:00:00 .
Let us consider below example query:
SELECT *
FROM orders
WHERE order_date BETWEEN '2020-01-01' AND '2020-03-03';
SELECT *
FROM orders
WHERE order_date >= '2020-01-01' AND
order_date <= '2020-03-03';
-- Here the output does not include the 3rd row of order_date '2020-03-03 03:04:00'
-- It is because the time part is not given in the end_date.
# order_id customer_name order_date total_orders
--------------------------------------------------------------------
1 Rose 2020-01-08 00:00:00 19
2 Jack 2020-02-03 00:00:00 4
-- So, the above query is effectively the same as the below query.
-- Since date '2020-03-03 03:04:00' is greater than '2020-03-03 00:00:00', the 3rd row is not included in the output
SELECT *
FROM orders
WHERE order_date BETWEEN '2020-01-01 00:00:00' AND '2020-03-03 00:00:00';
SELECT *
FROM orders
WHERE order_date >= '2020-01-01 00:00:00' AND
order_date <= '2020-03-03 00:00:00';
-- To include all the data of the ending date, we must include the time part as '23:59:59.999' in the end date as below.
SELECT *
FROM orders
WHERE order_date BETWEEN '2020-01-01' AND '2020-03-03 23:59:59.999';
SELECT *
FROM orders
WHERE order_date >= '2020-01-01' AND
order_date <= '2020-03-03 23:59:59.999'
--The output also included the 3rd since the time part is also provided in end date
# order_id customer_name order_date total_orders
------------------------------------------------------------------------
1 Rose 2020-01-08 00:00:00 19
2 Jack 2020-02-03 00:00:00 4
3 Jack 2020-03-03 03:04:01 40And that’s how to select dates between the two given dates in SQL!
Up Next:
