Databases Reporting with SQL Date and Time Functions Practice Session

Sean Flanagan
Sean Flanagan
33,224 Points
Posted by Sean Flanagan
Sean Flanagan
33,224 Points

Loans overdue

Hi. How's my solution for this challenge?

SELECT * FROM loans WHERE returned_on > return_by;
William Bailey
William Bailey
4,585 Points

If i remember correctly you are looking to query for all the books loaned out that are overdue. If this is the case then your query is almost perfect. You should also consider any books that were returned on the same day they were due -> Hint: [ There is room in your query somewhere for an "equality" check ;) ].

You Got This!

3 Answers

Troy Hooker
Troy Hooker
3,282 Points
Troy Hooker
Troy Hooker
3,282 Points

I think we want loans with a return_by date which is in the past and with no returned_on date (book hasn't been returned.) This seemed to work:

SELECT * FROM loans WHERE return_by < DATE("now") AND returned_on IS NULL;

Yassin Chiguer
Yassin Chiguer
5,228 Points
Yassin Chiguer
Yassin Chiguer
5,228 Points

The following query worked for me. Checks your current date (now) to yesterday's date (-1 day) for the return_by value. That's my understanding from the tutorial.

select * from loans where return_by = DATE("now", "-1 day")

Marcus Grant
PRO
Marcus Grant
Pro Student 2,546 Points
Marcus Grant
Marcus Grant
Pro Student 2,546 Points

Since I noticed that the dayys in this challenge are dynamic, I came up with the following which works:

SELECT * FROM loans WHERE return_by < DATE("now");

Since the book might be due to returned today, it won't count as it's not overdue. However, if the return date is null and is any day before today I will get the correct list of overdue books.