Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Filtering results by date can be handy for finding entries before or after a specific date. In this video we'll explore filtering by dates.
Relational Operators
There are several relational operators you can use:
-
<
less than -
<=
less than or equal to -
>
greater than -
>=
greater than or equal to
These are primarily used to compare numeric and date/time types.
SELECT <columns> FROM <table> WHERE <column name> < <value>;
SELECT <columns> FROM <table> WHERE <column name> <= <value>;
SELECT <columns> FROM <table> WHERE <column name> > <value>;
SELECT <columns> FROM <table> WHERE <column name> >= <value>;
Examples:
SELECT first_name, last_name FROM users WHERE date_of_birth < '1998-12-01';
SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015;
SELECT name, description FROM products WHERE price > 9.99;
SELECT title FROM movies WHERE release_year >= 2000;
The powerful comparison
operators we've been using
0:00
can also be used with date types.
0:03
Let's look at a couple of
examples to see how they work.
0:05
Let's take a look at our loans table and
ask some real life questions.
0:08
Let's find out what are all the loans
that happened before December 13th, 2015?
0:12
We can use a where cause.
0:18
Remember, a WHERE clause is column,
operator, and value.
0:21
The column to answer this
question is loaned_on,
0:27
And the value is 2015-12-13.
0:35
Now, which operator do we use?
0:40
Less than, or greater than?
0:42
Since we're looking for dates before
the value on the right hand side,
0:45
we want to use the less than operator.
0:48
All dates before a given date value
are considered less than the value.
0:53
All these results are the loans
before December 13th, 2015.
0:58
Okay, let's take a look
at another question.
1:02
Imagine today is December 18th, 2015.
1:06
We want to send out a reminder to all
those who have books due back soon.
1:09
So the question would be which
books are due back soon?
1:14
Let's copy and paste this template
here and fill in the condition.
1:18
For the condition this time we
want to use the return by column.
1:23
The value is 2015.
1:31
-12-18.
1:37
Since we wanted dates after a date value,
1:42
we use the greater than
operator in this case.
1:44
Awesome.
1:50
This query has returned
all loans whether or
1:51
not the books have already
been returned to the library.
1:54
A little later on I'll show you how to
make this query return just the loans
1:58
that haven't been returned to the library.
2:02
Now we can find the patrons
with the IDs of 1 or 3,
2:05
send them an email reminder
to return their books.
2:08
We don't need to email patron 4 because
they returned their books on the 17th.
2:24
You need to sign up for Treehouse in order to download course files.
Sign up