How to use DateDIFF function in mysql table-CodePudding

I have a dataset in my library management system and I have used below query to get specific field only.

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

now I need DATEDIFF function to get the date difference between today date and ReturnDate if there is no any receiveddate value.Further I dont need negative values as well. As an example if currdate()<ToBEReturnDate the value should be zero(mean that there are more time to user to return the book) if not the difference should be there.

FInal output should be as follows,

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

Are there any method to use datediff function as I required?

CodePudding user response：

I think it is :

SELECT  BookName,IssuedDate,ToBEReturnDate,BookStatus ,
CASE WHEN  GETDATE()<ToBEReturnDate THEN 0 ELSE DATEDIFF(DAY , ReturnDate , 
 GETDATE()) END AS DateDifference
 FROM issuedbooks
 WHERE  ReceivedDate IS NULL

CodePudding user response：

I believe we do need the receiveddate column, which is missing in your original query. Besides, being a date date type,the receiveddate column does not allow an empty string as its value, which we need to store it as null but can later be displayed as an empty string. Here is the code I wrote and tested in workbench.

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
;