如何在mysql表中使用DateDIFF函数

Question

我的图书馆管理系统中有一个数据集，我使用下面的查询仅获取特定字段。

select BookName,IssuedDate,ToBEReturnDate,BookStatus from issuedbooks;

RESULT:
Book Name   Issued Date Return Date   ReceivedDate     Book Status
Book 1        5/1/2022   5/14/2022                     Not Received
Book 2        5/2/2022   5/15/2022                     Not Received
Book 3        5/3/2022   5/16/2022                     Not Received
Book 4        5/4/2022   5/17/2022     5/24/2022         Received
Book 5        5/5/2022   5/18/2022                     Not Received
Book 6        5/5/2022   6/10/2022                     Not Received

现在，如果没有任何收到的日期值，我需要 DATEDIFF 函数来获取今天日期和 ReturnDate 之间的日期差。此外，我也不需要负值。例如 if currdate()<ToBEReturnDate 的值应该为零（意味着用户有更多时间还书），如果不是，则应该存在差异。

最终输出应如下所示，

Book Name   IssuedDate  ReturnDate  ReceivedDate    BookStatus    DateExpire
Book 1      5/1/2022    5/14/2022                   Not Received     15
Book 2      5/2/2022    5/15/2022                   Not Received     14
Book 3      5/3/2022    5/16/2022                   Not Received     13
Book 4      5/4/2022    5/17/2022   5/24/2022         Received        7
Book 5      5/5/2022    5/18/2022                   Not Received     11
Book 6      5/5/2022    6/10/2022                   Not Received      0

有什么方法可以根据我的需要使用 datediff 函数吗？

Answer 1

我相信我们确实需要 receiveddate 列，该列在您的原始查询中缺失。此外，作为 date 日期类型，receiveddate 列不允许使用空字符串作为其值，我们需要将其存储为 null，但稍后可以显示为空字符串。这是我在工作台中编写和测试的代码。

select BookName as 'Book Name',IssuedDate,ToBEReturnDate as ReturnDate, ifnull(receiveddate,'') as ReceivedDate , BookStatus,
case 
when receiveddate is null then if(datediff(current_date(),tobereturndate)>0, datediff(current_date(),tobereturndate), 0)
else if(datediff(receiveddate,tobereturndate)>0, datediff(receiveddate,tobereturndate), 0)
end as DateExpire
from issuedbooks
;

Answer 2

我认为是：

SELECT  BookName,IssuedDate,ToBEReturnDate,BookStatus ,
CASE WHEN  GETDATE()