如何在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()