How to use DateDIFF function in mysql table

Question

I have a dataset in my library management system and I am using the query below to get only specific fields.

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, if there is no received date value, I need the DATEDIFF function to get the date difference between today's date and ReturnDate. Also, I don't need negative values. For example if currdate()<ToBEReturnDate should have a value of zero (meaning the user has more time to return the book), if not, there should be a difference.

The final output should look like this,

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

Is there any way to use the datediff function for my needs?

Answer 1

I believe we do need the receiveddate column, which is missing from your original query. Also, being a date date type, the receiveddate column does not allow an empty string as its value, we need to store it as null but can later display it as an empty string. This 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
;

Answer 2

I think so:

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