Databases Reporting with SQL Date and Time Functions Practice Session

Loans Due solution

Hi,

The Loans Due practice question was nice. I've researched and came up with this:

SELECT * FROM loans WHERE return_by BETWEEN DATE('now', 'weekday 0', '-6 days') AND DATE('now', 'weekday 0') AND returned_on IS null;

Did anybody found a cleaner/more compact solution? :)

Cheers!

8 Answers

Denny Schouten
Denny Schouten
8,242 Points

Hi this is my solution SELECT * FROM loans WHERE STRFTIME("%W", return_by) = STRFTIME("%W", "now") AND returned_on IS NULL;

SELECT * FROM loans WHERE return_by BETWEEN DATE("now") AND DATE("now", "+7 days") AND returned_on IS NULL;

Alexandru Palita
Alexandru Palita
14,254 Points

SELECT * FROM loans WHERE returned_on > return_by OR returned_on IS NULL;

:))))

Sean Flanagan
Sean Flanagan
33,224 Points

How's mine?

SELECT * FROM loans WHERE return_by BETWEEN DATE("now", "+1 week") AND returned_on IS NULL;
Yassin Chiguer
Yassin Chiguer
5,228 Points

select * from loans where return_by BETWEEN DATE("now", "+ 7days") and returned_on is null

DeAndre' McDade
DeAndre' McDade
2,937 Points
SELECT  *
FROM  loans
WHERE return_by BETWEEN DATE('now', 'weekday 0') AND DATE('now', 'weekday 0', "+6 days")
      AND returned_on IS NULL
;
Roger Dailey
Roger Dailey
14,887 Points

This is how I wrote it: SELECT * FROM loans WHERE return_by BETWEEN DATE("now", "-1 day") AND DATE("now", "+6 days") AND returned_on IS NULL;

When I completed this it was the week of 02/13/17 through 02/19/17 and the day I completed it on was 02/14/17.

Jonatan Spahn
Jonatan Spahn
6,362 Points

I did this on today so 3/23 and this is how I did it.

SELECT * FROM loans WHERE return_by BETWEEN DATE("now", "-7 days") AND DATE("now", "+1 days");