Compare Dates in SQL:
Everything You Need To Know

Generate SQL examples for comparing dates

As the first step, let us fill several tables with the test data using different date formats (like DATE and DATETIME) to make the examples more illustrative. Usually, to populate a table with test SQL data, you would have to create one and use the INSERT statement to fill it in with values manually. In this tutorial, we are going to use Data Generator for SQL Server to populate the demo tables with meaningful data.

How to compare dates in SQL Server

When comparing SQL tables that contain DATE values, not many issues should arise in the process since all the dates are in the same format. However, if you are not an experienced user and just starting to delve into SQL Server, writing the comparison queries manually can be a challenge.

Using dbForge Data Compare for SQL Server, you can arrange a flawless SQL database comparison, quickly analyze SQL data differences, generate comparison reports, and synchronize SQL Server data by means of the convenient wizard instead of writing countless code lines.

The tool allows you to easily configure the data comparison. With its convenient user interface you can select any database, backup, or scripts folder. Morewover, you will be able to compare the results of custom queries.

Using comparison operators

Comparison operators can be used in a logical statement in order to determine whether variables or values are equal or different. These operators can come in handy when you need to compare two dates in SQL. The operators include:

• = - equals
• > - greater than
• < - less than
• >= - greater than equal
• <= - less than equal
• <> - not equal

Let us take a closer look at an example of how to compare dates in SQL Server using a basic SQL query:

SELECT column_1, column_2, date_column,
FROM table_name
WHERE date_column >= 'YYYY-MM-DD';
                                    

In the syntax above, the output will contain three columns: column_1, column_2, and date_column from the table_name table. However, it will include only those rows that contain the date that is greater or equal to the specified one.

Using BETWEEN clause

Earlier, we provided you with an example of how to compare dates in SQL Server using comparison operators. If you are going to compare dates within a range, it is convenient to add the BETWEEN operator to your query:

SELECT SUM(column_1) as "comparison result"
FROM table_name
WHERE date_column BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';
                                    

The query above returns only those rows from column_1, that contain the specified date range in date_column.

The fast way to compare two dates with time

One of the fairly common problems in administering large databases is that tables can store dates in different formats. Before comparing such tables using code, you will have to bring all of them to a common denominator (convert to the same format). Fortunately, there is an easier way to go about comparing dates in DATE and DATETIME formats — Data Compare for SQL Server.

This powerful SQL comparison tool easily copes with big data and supports native SQL Server backups. Besides being able to compare dates in different formats, the tool visualizes the differences in comparison results by highlighting them in a darker color.

Compare dates with TimeStamp

Following the basic pattern, you can also make your date comparison more precise by adding a timestamp to the query:

SELECT column_1, column_2, ... date_column,
FROM table_name
WHERE date_column >= 'YYYY-MM-DD hh:mm:ss';
                                    

In Data Compare for SQL Server, the comparison results will be represented as a grid divided into different tabs: All, the results that are found Only in Source and Only in Target, Different and Identical results.

SQL comparison dates with DATETIME

We can use different commands and clauses in SQL with DATETIME as well. Suppose, we want to retrieve all the records done over a certain period of time. In this case, the query will look along the lines of:

SELECT column_1, column_2, ... date_column,
FROM table_name
WHERE date_column BETWEEN 'YYYY-MM-DD hh:mm:ss' AND 'YYYY-MM-DD hh:mm:ss';
                                    

Comparing two dates in DATE and DATETIME formats

As we have already mentioned earlier in this article, before comparing two dates in different formats, you have to convert them to one. Moreover, retrieving all the records along with their timestamps might be one hefty task for a large-scale database. To avoid overloading our database, we can use CAST or convert the DATETIME to DATE:

SELECT column_1, column_2, ... date_column,
FROM table_name
WHERE CAST(date_column AS DATE) < 'YYYY-MM-DD';
                                    

As you can see, the results only contain the date without the timestamp in Data Compare for SQL Server.

Manage date comparison results

Data comparison document gives you a clear view of differences found in the databases and offers rich settings to analyze them.

Filter comparison results in the grid using the Filter list to narrow down the results.

See differences between compared objects in the text comparer under the grid.

If required, include only necessary objects into synchronization by selecting check boxes next to the needed objects. To exclude objects from synchronization, clear the corresponding checkboxes.

Export date comparison results

The perks of the dbForge data comparing tool do not end when the data comparison itself is over. You can also conveniently export the results to the format of your choice. It can be Excel, HTML, or CSV. Besides, you can save the comparison results in automatic mode.

Read on to find out how to compare data differences between two tables in SQL Server with SSIS and dbForge tools.