I have a dataset in my library management system and I have used below query to get specific field only.
x
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?
Advertisement
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