How to Compare Dates in SQL

Agenda

This article will look at yet another interesting topic in SQL commonly asked in interviews – How to Compare Dates or Date type date in SQL. We will look at different approaches with examples for a clear description. At first, let’s have a quick look at the Date data type in SQL.

Introduction to DATE in SQL

The DATE datatype in SQL has its use mainly to store data in the format of a date. This data type stores only date without the time and information related to timezone. We use this data type to store dates rather than storing as a VARCHAR or any other text type because we can use many inbuilt functions for DATE types which makes querying easier.

Each RDBMS provides its own implementation of DATE and a set of inbuilt functions. The DATE is present in MYSQL Server, MS SQL Server, and ORACLE Database respectively each having its own implementation.

Declaration Syntax in SQL:

Note: In MYSQL and Microsoft SQL Server, DATE  is stored in the format ‘YYYY-MM-DD’ whereas in ORACLE it is stored in ‘DD-MM-YYYY’, so keep in mind the format while using the respective database server.

Comparing Dates in SQL with Examples

We can compare dates while querying over rows in our database. To avoid confusion in the following examples we will discuss the query working for both MYSQL and SQL Server only.

Now, let us consider a sample Table COURSE given below:

The above table Course contains the records of names of Courses along with the information of the Start Date and End Date of each course. So, it is evident from the table that we need the Start_Date and End_Date should be of data type DATE.

Let us build the schema and insert the above records:

Here, we can see the Start_Date and End_Date attributes have datatype DATE. We enter the dates in ‘YYYY-MM-DD’ format.

Now, we can compare dates in two ways.

Using Comparison Operators

We can compare dates using Comparison Operators in SQL like, = (Equals), < (Less than), >(Greater than), <= (Less than Equal), >= (Greater than Equal), <> (Not Equal), etc.

Suppose, from the above-given records we have to Find the Courses which Start from October 6, 2021

The query for this would be:

Output:

So, we see with the Equals Operator we can compare the date with any date we want as long as we follow the same format used while defining. Again, assume we want to Find the Courses which ends before November 11,2021.

The Query for this problem would be :

Output:

We can see the courses which end before November 11,2021 to query the result we simply use the < (Less Than) Operator and compare the END_DATE of each course with the given date.

Now for the last example using Comparison operators, let us assume we need to Find Course which Start After October 5,2021 and End on or before November 21,2021.

Let us look at the query for this:

Output:

Here, we use > Operator to get START_DATE of courses after October 5 and we use <= (Less than) Operator since we need the courses with END_DATE on or before November 11. We use the AND Clause to combine both the comparisons into a single condition.

Further reading:

Find second highest salary in SQL

Read more →

Difference between WHERE and HAVING clause in SQL

Read more →

Using the BETWEEN Clause

Instead of using the comparison operators like we did in the example above, we can use the BETWEEN clause for comparing dates within a range. We just need to provide the range in date formats and combine it with the AND statement.

There is however a note to using the Between clause, the BETWEEN clause filters record inclusive of the range values. In other words, it works like <= and >= operators combined. Also, we can compare and add a condition for only one attribute when using the BETWEEN Clause.

Let us understand this with an example. Suppose we need to Find Number of Ongoing Courses which have Started from October 3,2021 to the date October 11,2021. The query would look like this:

Here, we see the use of BETWEEN Clause, It segregates the courses having START_DATE between October 3 to October 11 inclusive of the range. Then, we return the Count of rows satisfying the given condition. For the table above, only 3 Courses will satisfy the condition.

Output:

Note: The Between Clause will filter records inclusive of the range provided. So, if we want to retrieve records before and after the given date or value we recommend using Comparison Operators like < and > .

Comparing Dates for DATETIME Datatypes/Formats

Now, let us assume we were working with datatypes of type DATETIME and not DATE, Would it still be possible to compare DATETIME values in a column? Well, Yes we can do so keeping in mind some rules while querying results. First, Let’s have a quick look into DATETIME datatype in SQL.

The DATETIME data type is used for storing data with both date and time values. It can store the date as well as the Timestamp of the timezone. In SQL, the DATETIME values are retrieved and displayed in YYYY-MM-DD hh: mm: ss format (In MYSQL).

Similar to DATE, each RDBMS provides its own implementation for DATETIME as well. For Example, Microsoft SQL Server defines DATETIME as a date combined with the time of the day along with the fractional seconds, based on a 24-hour clock.

Hence the format is YYYY-MM-DD hh:mm:ss.fff where hh represents hours, mm represents minutes, ss represents seconds, and ff shows seconds infractions or milliseconds. For more information regarding DATETIME refer to Microsoft Docs.

Now, to compare DateTime let us assume a sample table Transaction given below:

This table contains records of a Transaction for a user with the transaction type, the amount along the Date and time of the occurrence of the said transaction.

Let us define the schema of the table shown above and insert the records:

Now, Suppose we want to Find All the Transactions that were done on October 2,2021 and if write this query :

We get this Output:

This is not the correct output we expected as there are multiple transactions done on the given date. The reason we get this output is that while comparing it with = (Equals) we mentioned only the date, not the timestamp associated with it.

Even if we give the timestamp it will compare it with the given timestamp only. So, there are two ways to do this and get all transactions that occurred on October 2,2021.

Using Comparison Operator with TimeStamp

We can write the query by putting the timestamp with the date. Now, to get all the transactions we will use the <= operator. Let’ see the query for this problem:

Output:

Here, we can see all the transactions that were done on October 2,2021. We simply got all transactions before the time 23:59:59  on October 2,2021.

Casting DATETIME as DATE without Timestamp

Well, writing queries by mentioning timestamps to get all the transactions or orders done before a certain date can be a hefty task. So, to avoid this we can Cast or Convert the DateTime to Date type, then we can just enter the date in our query and get all the transactions for the given date.

There are some inbuilt functions provided by the RDBMS Server in use. In MYSQL Server, there are functions such as CAST(), DATE() to convert the String or a Datetime to Date type. SQL Server provides many functions similar to this like CAST(), CONVERT(), etc. We will use the CAST() Function to write this query.

Let us look at the query:

Output:

 

Using Between Clause with DATETIME

We can use different commands and clauses in SQL with DATETIME as well.  Suppose, we want all transactions done over a period of time. For instance, if we want to Find the Transaction that occurred between October 2,2021 11:00 AM and October 3,2021 1:00 PM.

We will use the BETWEEN Clause for this. So let’s look at the query:

Output:

That’s it for how to compare dates in sql, you can try out the examples discussed above in your SQL Compiler and get a clear idea about the topics discussed above.