Updated March 13, 2023
Introduction to Compare Date in SQL
DATE Comparisons in SQL is a collective term used for methods used in comparing data values in date datatype format. The methods used for date comparison varies across SQL database servers. But usually, there is a basic procedure for it. If the date is not already in the date format, then use data type conversion functions and convert it from string to DATE data type and then make the relevant comparison. While comparing dates, we can compare a date value element wise that is comparing days, months, years, weeks, etc. extracted from it. We will be learning about all these things in this post. Let us discuss the Compare Date in SQL.
Syntax 0f Compare Date in SQL
The basic syntax used for comparing dates in SQL is as follows :
SELECT column_name1, column_name2, …
FROM table_name1
WHERE column_name1 :: date comparison_operator [, >,<,=, !=, ...] comparision_expression :: dateParameters of Compare Date
The parameters used in the above syntax are as follows :
- column_name1, column_name2, …: Field or columns which we want to fetch for the final result set.
- table_name1: The database table from which the said columns will be fetched from.
- column_name1: Date value either in date data type or string format that can be converted to date using :: DATE (data type conversion function). This is specific to PostgreSQL and might vary according to the database server used by you.
- comparision_expression: Another date value or date data type convertible value with which column_name1 will be compared.
You might notice that date comparison is nothing unusual, it’s just that we might have to compare it element wise sometimes or might require some data type conversions. That’s it. However, having learnt the syntax and parameters used, we should now try some examples for date comparisons.
Below are some examples of Compare Date in SQL:
In order to illustrate the methods used for date comparisons in SQL, let us create a dummy table called “e-transactions”. It contains details pertaining to orders made at an e-commerce site. We can use the following code snippet to create this table.
Query:
CREATE TABLE e_transactions (
order_id character varying(255),
order_date date,
first_view date,
order_amount numeric,
customer_id character varying(255)
);Output:
Next, use the following INSERT query to insert some records in the e-transactions table to work with.
Query:
INSERT INTO public.e_transactions(
order_id, order_date, first_view, order_amount, customer_id)
VALUES ('O1001','2020-01-12','2020-01-02',4532,'UK10201'),
('O1002','2020-01-12','2020-01-01',1522,'UK10223'),
('O1003','2020-01-14','2019-12-23',1930,'UK10201'),
('O1004','2020-01-13','2019-11-19',532,'UK10202'),
('O1005','2020-01-12','2020-01-01',1789,'UK10202'),
('O1006','2020-02-12','2020-02-02',3976,'UK10216'),
('O1007','2020-02-13','2020-01-26',3976,'UK10223');Output:
The query returned successfully. The table after value insertion looks something as follows:
Query:
select * from e_transactions;Output:
Examples to Implement Compare Date in SQL
Below are the examples of Compare Date in SQL:
Example #1
Find the customer_id, order_id, order_date and order_amount for all the orders placed after 1st January 2020.
Query:
SELECT customer_id, order_id, order_date, order_amount
FROM e_transactions
WHERE order_date >= '2020-01-01';Output:
Explanation: The first query is simple to understand and it basically compares two dates. But date comparisons in SQL can be tricky at times as we come across situations when the date is in the string format or any other format which is not DATE or timestamp format. In such situations, we first have to convert them to date type and then make a comparison between the dates. The second query shows the same.
Example #2
Find the details of all the orders which the customer has been eyeing before 1st January 2020 but bought it only after 12th January 2020.
Query:
SELECT customer_id, order_id, first_view, order_date, order_amount
FROM e_transactions
WHERE first_view < '2020-01-01' AND
order_date >= '2020-01-12';Output:
Example #3
Find the total revenue collected from the orders placed between 1st and 15th January 2020.
Query:
SELECT SUM(order_amount) as "total revenue"
FROM e_transactions
WHERE order_date BETWEEN '2020-01-01' AND '2020-01-15';Output:
Example #4
Find the details of all the orders for which order date is similar to order_dates of orders having first view dates between 1st Jan 2020 and 15th Jan 2020.
Query:
SELECT customer_id, order_id, order_amount
FROM e_transactions
WHERE order_date IN ( SELECT order_date
FROM e_transactions
WHERE first_view
BETWEEN '2020-01-01' AND '2020-01-15');Output:
This example might not make perfect sense but it has been kept on purpose to illustrate date comparisons with subqueries involved.
Example #5
Find all the details of orders where the difference between the first view date and order date is less than or equal to 10 days.
Query:
SELECT customer_id, order_amount, order_date, first_view
FROM e_transactions
WHERE date_part('day',age(order_date, first_view)) <= 10;Output:
Explanation: This example is interesting and is very useful in practical scenarios. Here, we have used date_part() and age() functions. Date_part is used to get a specific part of the date and age is used to calculate the time elapsed between two given dates.
Example #6
Find the number of orders and distinct customers monthwise who have viewed a product after 1st Jan 2020.
Query:
SELECT extract ('month' from order_date) AS "Month",
count(order_id) AS "orders",
count(distinct customer_id) AS "distinct customers"
FROM e_transactions
GROUP BY 1 , first_view
HAVING (first_view >= '2020-01-01');Output:
Example #7
Find all the details of orders placed within the last 12 hours.
Query:
SELECT *
FROM e_transactions
WHERE order_date >= NOW() - interval '12 hour';Output:
The query returned successfully, But since we do not have any orders which were placed in the last 12 hours, we do not get any results.
Conclusion
The date comparisons in SQL can get difficult at times but it just involves the correct conversion of data values to date data type or extraction of the correct date element for comparison.
Recommended Articles
We hope that this EDUCBA information on “Compare Date in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
