How to use DateDIFF function in mysql table

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?

Answer

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

Advertisement

Answer