How to Compare Date Values in SQL

By /
Featured Img

If you have been writing SQL queries for some time you must have faced challenges while comparing dates. The reason is that the format of the table date must match the format of the input data. There might be certain situations in which TimeStamp and DATETIME formats can be used instead of date. In this tutorial, we will learn how to compare dates in different formats with the help of practical examples.

Understanding Date Data Types

Dates and times are stored using specific data types designed to exactly fit temporal information in SQL databases. These data types vary in accuracy and performance, which allows developers to choose the right one based on their application needs. Here are some common date data types:

  • DATE: The DATE data type stores only date component information that does not contain a time component. This is helpful when you need to depict dates without taking the time of day into account
  • DATETIME/TIMESTAMP: These data types store the information of both date and time. The difference between them is the accuracy and extent of support.
  • DATETIME: DATETIME normally stores date and time with seconds accuracy. Where precise timestamps are required, but subsecond accuracy is not required.
  • TIMESTAMP: TIMESTAMP is similar to DATETIME but can have higher accuracy, usually fractions of a second. It also typically stores time in UTC format, which makes it useful for applications that require global time synchronization. These data types loosen up the efficient storage and fetching of dated information and ensure accuracy and precision in database operations.

It is important to know the specific uses and limitations of each data type in our database system.

Also Read: MySQL DAY(), MONTH() and YEAR() – Date Functions in MySQL

Compare Date Values in SQL

SQL supports standard comparison operators like ‘=’, ‘>’, ‘<‘, ‘>=’, ‘<=’, ‘<>’, etc. We can use these operators to compare date values directly.

Using BETWEEN Clause

If we want to compare dates with a range, it is convenient to add the BETWEEN operator to your query. These operators include, “<“, “>, “>=”, “=”, “<=”, “=”, “<> or !=”.

Syntax

SELECT SUM(column01) as "comparison result set"
FROM tablename
WHERE date_column BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';Code language: SQL (Structured Query Language) (sql)

This query will return those rows from the column01, that contain the specified date range in date_column.

Example

Let’s say we have a table ‘sales’ given below:

Sales
Sales

If we want to find the sum of sales amount between the period of January 2022 and January 31, 2022.

SELECT SUM(amount) as "total_sales"
FROM sales
WHERE transaction_date BETWEEN '2022-01-01' AND '2022-01-31';Code language: SQL (Structured Query Language) (sql)

Output

Example 1

Compare Dates with TimeStamp

Timestamps include both date and time components. If we compare timestamps with the DATE data type, SQL implicitly considers the time portion but treats it as midnight (00:00:00).

Following the basic example, we can also improve the accuracy of out-date comparison by adding a timestamp to the query.

Syntax

SELECT column_1, column_2,...date_column,
FROM tablename
WHERE  date_column >= 'YYYY-MM-DD hh:mm:ss';Code language: SQL (Structured Query Language) (sql)

In data comparison for SQL Server, the comparison results will be displayed as a grid divided into tabs.

Example

Let’s say we have a table ‘orders’ given below:

Orders
Orders

If we want to return records from the orders table, where date is on or after Feb 27, 2024.

SELECT order_id, order_date, order_amount
FROM orders
WHERE order_date >= '2024-02-27 00:00:00';Code language: SQL (Structured Query Language) (sql)

Output

Example 2

Compare Dates with DATETIME

The DATETIME data type has components for both date and time. When we compare DATETIME with DATE, SQL treats the DATETIME value as a DATE by ignoring the time component.

Let’s say we want to fetch all entries that are created during a specific time period. In this situation, the query will be:

Syntax

SELECT column_1, column_2,...date_column,
FROM tablename
WHERE  date_column BETWEEN 'YYYY-MM-DD hh:mm:ss' AND 'YYYY-MM-DD hh:mm:ss';Code language: SQL (Structured Query Language) (sql)

Example

If we want to fetch the record for all orders placed between the period of Feb 28, 2024, at 00:00:00 and Feb 29, 2024, at 23:59:59 from the orders table.

SELECT order_id, order_date, order_amount
FROM orders
WHERE order_date >= '2024-02-28 00:00:00' AND '2024-02-29 23:59:59';Code language: SQL (Structured Query Language) (sql)

Output

Example 3

Comparing Two Dates in DATE and DATETIME

Before comparing dates in different formats, it’s important to convert them to a consistent one. To prevent overloading, we can use CAST or convert the DATETIME to DATE.

Syntax

SELECT column_1, column_2,...date_column,
FROM tablename
WHERE CAST(date_column AS DATE ) < 'YYYY-MM-DD';Code language: SQL (Structured Query Language) (sql)

Example

If we want to fetch the records of all orders where the date portion is before Feb 28, 2024, from the orders table.

SELECT order_id, order_date, order_amount
FROM orders
WHERE CAST(order_date AS DATE) < '2024-02-28';Code language: SQL (Structured Query Language) (sql)

Output

Example 4

Conclusion

In this tutorial, we have learned multiple methods for comparing dates in SQL. Whether through standard operators, the BETWEEN clause, timestamps, DATETIME, or format conversion, SQL offers methods for handling date comparisons. Each method fulfils different needs, from simple date ranges to precise timestamp evaluations. By mastering these techniques, developers can efficiently manage data-related tasks.

Reference

https://stackoverflow.com/questions/45286762/sql-server-date-comparison