Comparing dates in SQL might be rather challenging, especially for beginners. The reason is that the format of the date in the table must match the format of the input date. There might be various scenarios in which TimeStamp and DATETIME formats can be used instead of date. In this article, we are going to compare dates in different formats with the help of a convenient tool — Data Compare for SQL Server.
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:
>- 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_nameWHERE 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_nameWHERE 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_nameWHERE 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_nameWHERE 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_nameWHERE 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.