CODEWITHSUNDEEP
mysql
disflux
disflux

Reputation: 439

MySQL WHERE Date > 15 Days

I have three tables. customers, DVDs, Movies. This is the last report I need to produce, and it's tripping me up. I currently have a field: dvd.Due_Date, which is the date that the product is due back. I need to retrieve all files where the Due Date is 15 days past the current date.

This is what I have so far:

    SELECT customer.customer_id,
 customer.customer_fname,
 customer.customer_lname,
 customer.customer_phone,
 customer.customer_email,
 dvd.DVD_ID, movie.Movie_Title,
 dvd.Rental_Date, dvd.Due_Date
 FROM customer INNER JOIN dvd
 ON customer.customer_id = dvd.customer_id 
INNER JOIN movie ON dvd.Movie_ID = movie.Movie_ID
 WHERE DATEDIFF(Due_Date, CURDATE() ) > 15

I'm not getting any errors, I'm just not getting any results back, even though I have multiple items listed as due date of Feb. 10th. I do get all of the information I want if I remove everything past the WHERE statement, so I know that is working at least.

Upvotes: 0

Views: 3954

Answers (2)

Spencer Wieczorek
Spencer Wieczorek

Reputation: 21565

For DATEDIFF if the first item is a smaller date than the second item then it returns a negative number (as such could never be larger than 16) and not a positive one. So flip them, you want the later date as the first argument:

... WHERE DATEDIFF( CURDATE(), Due_Date ) > 15

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I'm not sure what you mean by "all files where the Due Date is 15 days past the current date." However, try using logic like this:

SELECT c.customer_id, c.customer_fname, c.customer_lname, c.customer_phone, c.customer_email, d.DVD_ID, m.Movie_Title, d.Rental_Date, d.Due_Date
FROM customer c INNER JOIN
     dvd d
     ON c.customer_id = d.customer_id INNER JOIN
     movie m
     ON d.Movie_ID = m.Movie_ID
WHERE due_date >= date_sub(curdate(), interval 15 day);

You might want date_add() instead.

Upvotes: 1

Related Questions