Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Filtering by Dates

2:30 with Andrew Chalkley

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;
  • 0:00

    The powerful comparison operators we've been using

  • 0:03

    can also be used with date types.

  • 0:05

    Let's look at a couple of examples to see how they work.

  • 0:08

    Let's take a look at our loans table and ask some real life questions.

  • 0:12

    Let's find out what are all the loans that happened before December 13th, 2015?

  • 0:18

    We can use a where cause.

  • 0:21

    Remember, a WHERE clause is column, operator, and value.

  • 0:27

    The column to answer this question is loaned_on,

  • 0:35

    And the value is 2015-12-13.

  • 0:40

    Now, which operator do we use?

  • 0:42

    Less than, or greater than?

  • 0:45

    Since we're looking for dates before the value on the right hand side,

  • 0:48

    we want to use the less than operator.

  • 0:53

    All dates before a given date value are considered less than the value.

  • 0:58

    All these results are the loans before December 13th, 2015.

  • 1:02

    Okay, let's take a look at another question.

  • 1:06

    Imagine today is December 18th, 2015.

  • 1:09

    We want to send out a reminder to all those who have books due back soon.

  • 1:14

    So the question would be which books are due back soon?

  • 1:18

    Let's copy and paste this template here and fill in the condition.

  • 1:23

    For the condition this time we want to use the return by column.

  • 1:31

    The value is 2015.

  • 1:37

    -12-18.

  • 1:42

    Since we wanted dates after a date value,

  • 1:44

    we use the greater than operator in this case.

  • 1:50

    Awesome.

  • 1:51

    This query has returned all loans whether or

  • 1:54

    not the books have already been returned to the library.

  • 1:58

    A little later on I'll show you how to make this query return just the loans

  • 2:02

    that haven't been returned to the library.

  • 2:05

    Now we can find the patrons with the IDs of 1 or 3,

  • 2:08

    send them an email reminder to return their books.

  • 2:24

    We don't need to email patron 4 because they returned their books on the 17th.

You need to sign up for Treehouse in order to download course files.

Sign up