So verwenden Sie die DateDIFF-Funktion in einer MySQL-Tabelle

Question

Ich habe einen Datensatz in meinem Bibliotheksverwaltungssystem und verwende die folgende Abfrage, um nur bestimmte Felder abzurufen. selectBookName,IssuedDate,ToBEReturnDate,BookStatusfromissuedbooks;RESULT:BookNameIssuedDateReturnDateReceivedDateBookStatusBook15/1/2025/14/2022NotReceivedBook25/2/2

P粉226642568 · Answer

我相信我们确实需要 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
;

P粉418854048 · Answer

我认为是：

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