Filtering by Dates2: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.
There are several relational operators you can use:
<=less than or equal to
>=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>;
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