SQL: Compare Dates When You Search For Records

In SQL, comparing dates is a widely-used and very basic way to filter records. Is it easy to do? Yes. What are some of the most common ways to do an SQL date comparison, and what are the potential problems of which you need to be aware? We’ll be answering those questions in this blog post.

And while we’re on the subject, how would you like to learn SQL in just six days?

Basic Date Comparison

First, let’s look at the most basic way to compare dates in SQL.

Suppose you have a table named “STUDENTS” with a column labeled “BIRTHDAY” and you want to find all students born after July 1, 1992. The simplest way to do the query would be this:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY > '1992-07-01'

This will list every student whose birthday is after July 1, 1992.

Take a Closer Look

Pretty simple, right? But notice a couple of things.

First of all, take a look at the date. It assumes that the data in the BIRTHDAY column is in standard DATE format (YYYY-MM-DD). You need to be aware of the actual format of the dates that you’re comparing; we’ll take a look at some of the ways that date formats and data types can affect a date comparison a little later in this post. For now, keep date format in mind.

Now look at the comparison operator, > (“greater than”). As it stands, the query will return students born on July 2, 1992 or later. If you want to include students born on July 1, 1992, you need to change the operator to >= (“greater than or equal to”):

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01'

or else change the target date to June 30, 1992:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY > '1992-06-30'

Other Ways To Compare

And of course, you can use other operators to compare dates. Suppose you want to find all students born from July 1, 1992 through June 30, 1994. You could do it like this:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01' AND BIRTHDAY <= '1994-06-30'

But you could also use the BETWEEN operator:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY BETWEEN '1992-07-01' AND '1994-06-30'

And Watch That Format

We mentioned format a little earlier. The YYYY-MM-DD DATE is a standard SQL data type, but there are other ways of representing dates in SQL, and many dialects of SQL use date formats which are not common to all versions of the language.

Add to that all of the databases where dates are stored as strings, integers, or other non-date datatypes, along with all of the regional and national variations in representing dates, and the process of comparing dates in an SQL query can become more than a little complicated at times.

Date or Date and Time?

Consider, for example, the DATETIME data type. It’s common enough so that you’re very likely to encounter it — for example, it’s used by both MySQL and Microsoft SQL Server. It stores not just the date, but also the time: YYYY-MM-DD HH:MM:SS, where HH is hours, MM is minutes, and SS is seconds. Note that HH uses 24-hour time — the range is from 00 to 23. (SQL Server also includes optional fractions of a second, like this: YYYY-MM-DD hh:mm:ss[.nnn], where .nnn is a decimal fraction of a second with an upper range of .997. The last possible moment before midnight in SQL Server DATETIME format is 23:59:59.997.)

What does this mean in a date comparison query?

Consider our earlier example, where we’re looking for all students born on or after July 1, 1992:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01'

As shown, it won’t work with DATETIME, because it doesn’t include the time part of the data.

Making DATETIME Work

This query will work:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01:00:00:00'

But be careful. The following DATETIME query will not catch all students born from July 1, 1992 through June 30, 1994:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01:00:00:00' AND BIRTHDAY <= '1994-06-30:00:00:00'

If the TIME part of the BIRTHDAY column’s DATETIME data contains times other than 00:00:00, the query will miss anyone born after 00:00:00 on June 30, 1994. A student whose birthday is in the database as 5:37:00 on June 30, 1994, for example, would not be picked up by the query as shown.

This DATETIME query, on the other hand, will work:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01:00:00:00' AND BIRTHDAY <= '1994-06-30:23:59:59'

Or, when the data includes SQL Server-style fractions of a second:

SELECT *
FROM STUDENTS
WHERE BIRTHDAY >= '1992-07-01:00:00:00'AND BIRTHDAY <= '1994-06-30:23:59:59.997'

Strings and Other Things

But, as we mentioned above, dates aren’t always stored in a standard or semi-standard SQL date format. A date can be stored as a string: ‘19920701’ is a perfectly reasonable way to represent a date as a string.

There are ways to convert such a string to a date; Oracle SQL, for example, has the TO_DATE function, which can converts strings representing a wide variety of date formats to standard DATE format data.

You could use TO_DATE(‘19920701’, ‘yyyymmdd’) to convert the string ‘19920701’ to ‘1992-07-01’ in DATE format.

You have to be careful, though, when dealing with dates in string and other formats, because different countries and regions have different conventions for representing dates. While the United States uses mm/dd/yy or mm/dd/yyyy, Britain and France use dd/mm/yy or dd/mm/yyyy, and Japan uses yy/mm/dd or yyyy/mm/dd, to mention only a few examples.

A date search query based on date strings converted to DATE format using the wrong mask is unlikely to work; it will probably produce an error, or in the very least bad search results.

Where Do You Go From Here?

There’s much more to SQL date comparison and to SQL queries in general, and there is a strong demand for programmers with practical SQL skills. You can find a wide variety of online courses which will teach you everything about SQL, from the very basics to the most technically sophisticated topics, including such high-demand subjects as advanced Oracle SQL.