MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

How to Compare SQL Dates

intro

Learn everything you need to know about procedures, techniques, and best practices to compare SQL dates.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Comparing dates is a common operation in SQL, essential for retrieving time-relevant records from the tables in a database. Some examples of why to compare SQL dates? Retrieving records before a specific date or getting events between date ranges.

In this guide, you will discover what comparing dates in SQL means, how to do it with queries, and how to compare dates with DbVisualizer.

Let's dive in!

What Does It Mean to Compare SQL Dates?

Comparing dates in SQL means applying a comparison operator to two or more dates. The purpose of this operation is to determine the relationship between SQL dates. Here are some operations you can use to compare SQL dates:

  • Equality: Check if two dates are exactly the same.
  • Inequality: Check if one date is not the same as another date.
  • Before: Check if a date precedes another date.
  • After: Check if a date follows another date.
  • Null: Check if the date is NULL.
  • Range: Check if a date falls within a specified range of dates.

Most database systems provide operators and functions to perform these SQL compare date operations on a variety of date data types, including timestamps, dates, time zone dates, and more.

Date SQL Compare Syntax

In most scenarios, the syntax to compare SQL dates is:

Copy
        
1 date_1 operator date_2

Where operator is an arithmetic operator such as =, <> (or !=), <, <=, >, and >=. In MySQL, PostgreSQL, SQL Server, Oracle, and other database systems, these operators are used to work on two SQL dates as if they were numbers.

Note: For those operators to work, the two dates should be in the ANSI-standard YYYY-MM-DD format.

The above syntax is generally used in WHERE clauses to filter data when performing CRUD queries, as in the example below:

Copy
        
1 SELECT * 2 FROM table_name 3 WHERE date_1 **operator** date_2

Some date comparison operations require a different syntax, such as:

  • date_1 IS NULL for NULL checks;
  • date_1 BETWEEN date_2 AND date_3 for range checks.

Comparing Dates in SQL: Possible Scenarios

Now that you know how to compare SQL dates, you are ready to explore some real-world scenarios.

The queries in this section will be executed on the customers table below:

The Data view for the customers table
The Data view for the customers table

This is a MySQL table, but the following queries will work on any other major SQL database. To connect to a database and execute the sample queries, we’ll use DbVisualizer—the database client with the highest user satisfaction in the market. Note that any other database client will do.

Time to dive into the SQL compare dates operations!

Equality

Suppose you want to retrieve all clients born on February 18, 1995. This is how you can achieve that:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE birth_date = '1995-02-18'

Note that the date to the right of the equality operator must be in the YYYY-MM-DD format.

As expected, the query retrieves only the record associated with Sarah Wilson:

Executing the date equality query in DbVisualizer
Executing the date equality query in DbVisualizer

Inequality

Suppose you want to get all customers who were not born on February 18, 1995. Select them all with:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE birth_date != '1995-02-18'

This time, the query will return all customer records except for Sarah Wilson:

Executing the date inequality query in DbVisualizer
Executing the date inequality query in DbVisualizer

After

You now want all customers who were born after September 22, 1993, or on that date. You can retrieve them with this date SQL compare query:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE birth_date >= '1993-09-22'

Execute it, and you will get two records:

Executing the query in DbVisualizer
Executing the query in DbVisualizer

Before

This time, you need all customers who were born before September 22, 1993. The query is:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE birth_date < '1993-09-22'

Run it, and you will get these records:

Executing the query in DbVisualizer
Executing the query in DbVisualizer

Null

Dealing with NULL values in SQL is essential to avoid undesired results. In this example, you need to get only customers whose birth_date column is not NULL. That is as simple as writing the following query:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE birth_date IS NOT NULL
Executing the query in DbVisualizer
Executing the query in DbVisualizer

Keep in mind that some database management systems may act on NULL values differently than their counterparts—always refer to the documentation and (or) database blogs such as the one by DbVisualizer experts before acting on any values in the SQL realm.

Range

You now want all clients born between 1990 and 1995. Find out all of them with this query:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE date_of_birth BETWEEN '1990-01-01' AND '1995-12-31'

Equivalently, you can write:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE birth_date >= '1990-01-01' 4 AND birth_date <= '1995-12-31'

In MySQL, you can also use the YEAR() function and write:

Copy
        
1 SELECT * 2 FROM customers 3 WHERE YEAR(birth_date) BETWEEN 1990 AND 1995

In all three above examples, you will get the following records:

Executing the query in DbVisualizer
Executing the query in DbVisualizer

Et voilà! You just learned that comparing dates in SQL is straightforward!

Compare SQL Dates Visually in DbVisualizer

Comparing SQL dates also means checking whether the values of a date have changed from one transaction to the next. This is essential for keeping track of what is happening in your database.

Ideally, you should be able to compare database objects and records directly in the database client. That is exactly what the DbVisualizer Compare Data feature is all about!

Suppose you want to add a few rows in the customers table and update some records. Before making these changes, you should see at a glance how the data in the table changes in a “Before-After” view. Thanks to the “Compare” option you can reach from the right-click contextual menu, that view is just a couple of clicks away in DbVisualizer:

Enabling the Compare Data feature
Enabling the Compare Data feature

Note: The Compare Data feature is only available in DbVisualizer Pro.

This is the data comparison view you will get:

The Compare Grids popup to compare data in DbVisalizer
The Compare Grids popup to compare data in DbVisalizer

Pretty cool, isn't it? Learn more in the official documentation.

SQL Compare Dates: Best Practices

Here is a list of some best practices to compare SQL dates like a pro:

  • Explicitly compare dates using appropriate comparison operators like =, <, >, and so on.
  • Ensure that the dates to the left and right of the operator are in the same format to avoid compatibility issues.
  • If your dates include time components, consider whether you need to account for them in your comparisons. Depending on your requirements, you may need to truncate or round the dates for easier comparison.
  • When specifying date literals in your queries, use the YYYY-MM-DD format.
  • If your date column allows NULL values, handle them appropriately to avoid undesired results.

Conclusion

In this guide, you understood what it means to compare SQL dates and how to do it. Now you know that you can apply arithmetic operators to compare dates in SLQ as if they were numbers. Thanks to the examples shown here, you also learned when and how to use these operators.

The ability to visually compare data from two tables or cells within the database client is critical for data monitoring. This is exactly what the DbVisualizer Compare Data feature offers users.

DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!

FAQ

How to compare dates in SQL server?

To compare dates in SQL Server, use comparison operators like =, <, >, <=, >=, and BETWEEN on dates. When using date literals, make sure to specify them in the YYYY-MM-DD format. Another approach to SQL server date comparison involves using the DATEDIFF() function on two dates and then comparing its result with 0.

How to compare components of two dates in SQL?

To compare components of two dates in SQL, use MySQL date functions like YEAR(), MONTH(), and DAY() to extract specific components. On PostgreSQL, extract the date components with DATE_PART() while in SQL Server use DATEPART(). Then, compare these components using regular comparison operators.

Is it possible to compare SQL dates with DATEDIFF?

Yes, it's possible to compare SQL dates in T-SQL using DATEDIFF(). This function calculates the difference between two dates in terms of a specified date part (such as days, months, or years). By comparing the result of DATEDIFF() with a threshold value, you can determine if one date is greater, lesser, or equal to another in terms of the chosen date part as in this SQL Server example:

Copy
        
1 SELECT * 2 FROM table_name 3 WHERE DATEDIFF(day, date_1, date_2) > 0

How to compare two timestamps in SQL?

To compare two timestamps in SQL, you can use the same approaches for comparing dates in SQL presented in this article. All of them also work with timestamps.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL STUFF: Insert a String Into Another in SQL Server

author Antonello Zanini tags SQL SERVER 7 min 2024-07-18
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Types of Databases Explained

author Lukas Vileikis tags MySQL 6 min 2024-07-01
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

MySQL IFNULL - Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-06-13

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗