MySQL WHERE 日期 >15天 [英] MySQL WHERE Date > 15 Days

问题描述

我有三张桌子.客户、DVD、电影.这是我需要制作的最后一份报告，这让我很不舒服.我目前有一个字段:dvd.Due_Date，这是产品到期的日期.我需要检索截止日期比当前日期晚 15 天的所有文件.

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.

这是我目前所拥有的:

    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

我没有收到任何错误，我只是没有得到任何结果，即使我将多个项目列为截止日期为 2 月 10 日.如果我删除 WHERE 语句之后的所有内容，我确实会得到我想要的所有信息，所以我知道这至少是有效的.

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.

推荐答案

对于 DATEDIFF 如果第一项的日期小于第二项的日期，则它返回一个负数(因此永远不可能大于 16) 而不是正数.所以翻转它们，你希望以后的日期作为第一个参数:

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

这篇关于MySQL WHERE 日期 >15天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！