SQL Query Between Two Dates and Times

When working with some records in SQL, you may need to filter and retrieve records from specific dates and times. For example: If you have an online shopping site, you can filter the range on the dates and times you want from the records that store the purchase details of the users in the table. So, in this article, we’ll see how to write an SQL query to filter between two dates and times

Understanding Types of Date and Times

Before filtering dates and times in SQL, you need to know the types of dates and times in relational database management systems (RDBMS). Dates and times can be specified precisely in three types in RDMS.

• DATETIME: It combines a date and time value (e.g., ‘2023-07-07 14:30:00’).
• DATE: It stores a date value without a time (e.g., ‘2023-07-07’).
• TIME: It represents a specific time of day without a date (e.g., ’14:30:00′).

Regardless of how dates and times are stored in these three categories, you can easily filter them to a specific range using the BETWEEN operator. In these three types, we usually use the first two (DATETIME & DATE) methods, because we store time with date, and we store dates separately, but not only time. So the general syntax to retrieve a specific range of these two types (DATETIME & DATE) is:

SELECT * FROM tblName 
WHERE clmName BETWEEN 'start_date' AND 'end_date';

In this syntax replace ‘tblName‘ with your TABLENAME and ‘clmName‘ with COLUMN NAME where you have stored Dates and times. Replace starting date with your desired ‘start_date‘ and endingdate with your desired ‘end_date‘ of the range you are going to filter.

How to filter Data Between Two Dates in SQL?

To store the date in the RDBMS table, you must specify the data type “DATE” in the table column name, you want to store. Give your date in quotes in the ‘YYYY-MM-DD‘ format.

Example:

In the below example, we have created the table “Companies” with cmpID, cmpName and cmpFounded columns. We have initialized the datatype DATE to store the company’s founded date. After that, we insert 5 rows of records and insert Dates in the format “YYYY-MM-DD” in the cmpFounded column. Finally, we use BETWEEN operator and WHERE clause to filter records between two dates. Even when you filter you should specify Dates in the same format “YYYY-MM-DD”.

--Creating Table
CREATE TABLE Companies(
cmpID INT PRIMARY KEY,
cmpName VARCHAR(30),
cmpFounded DATE
);

--Inseting Values
INSERT INTO Companies VALUES(1, 'Twitter', '2006-03-21');
INSERT INTO Companies VALUES(2, 'Facebook', '2004-04-02');
INSERT INTO Companies VALUES(3, 'Amazon', '1994-07-05');
INSERT INTO Companies VALUES(4, 'Tesla', '2003-07-01');
INSERT INTO Companies VALUES(5, 'Google', '1998-09-04');

--Filtering Dates
SELECT * FROM Companies
WHERE cmpFounded BETWEEN '1990-01-01' AND '2000-01-01';

See records from ‘1990-01-01’ to ‘2000-01-01’:

Filtering the data between ‘2000-01-01’; and ‘2020-01-01’ using greater-than-or-equal-to and less-than-or-equal-to operators:

--Filtering Dates
SELECT * FROM Companies
WHERE cmpFounded >='1990-01-01' AND cmpFounded <='2000-01-01';

How to filter Data Between Two Dates and times in SQL?

If you want to store dates and times, when creating the table, you must specify the data type “DATETIME or DATETIME2” in the column where you want to store dates and times.

CREATE TABLE Birthdate(
  ID INT PRIMARY KEY,
  Name VARCHAR(30),
  Dates DATETIME 
);

You should store dates and times in the format “YYYY-MM-DD HH:MM:SS” and this time is 24 hours format. You should put carefully the ‘-‘ underscore between dates and the ‘:‘ colon between times, because the SQL server considers date and time only by looking at that special character. So, Once you have created the table with the appropriate date and time data type, you can insert values using the specified format. For example:

--Inseting Values
INSERT INTO Birthdate VALUES(1, 'Smith', '2003-10-21 14:30:00');
INSERT INTO Birthdate VALUES(2, 'Rob', '1989-04-02 10:50:40');
INSERT INTO Birthdate VALUES(3, 'Kevin', '1994-09-25 11:36:20');
INSERT INTO Birthdate VALUES(4, 'John', '2006-07-16 22:33:00');
INSERT INTO Birthdate VALUES(5, 'Steve', '1998-09-04 14:20:10');

Then we can write SQL query between two dates and times using BETWEEN operator, like:

--Filtering Dates and times
SELECT * FROM Birthdate
WHERE Dates BETWEEN '2000-10-21 14:30:00' AND '2020-01-01 14:30:00';

If you use the BETWEEN operator, the values ​​we check are checked inclusively. If you write a SQL query to filter records between two dates and times using greater than(>) and less than (<) operators, the values ​​we check are treated as exclusive.

--Filtering Dates and times
SELECT * FROM Birthdate
WHERE Dates > '2000-10-21 14:30:00' AND Dates < '2020-01-01 14:30:00';

Conclusion

So, you can use BETWEEN operator and WHERE clause or greater than(>) and less than(<) operator in SQL to retrieve data between two dates and times.