Loading

How to find out list of upcoming expiry items

How to find out list of upcoming expiry items

Prakash Mondal

4y1.2k

My database table: (Purchase-Details)

Table with value:

I want to find out the list of upcoming exipry medicines.

my Query:

SELECT * FROM PURCHASE_DETAILS

WHERE CONVERT(date,EXPIRY) BETWEEN CONVERT(DATE,'21-Dec-2021') AND CONVERT(DATE,'31-Oct-2022')

but error occur.

Please suggest me.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Accepted answer
To begin with, not sure why the dates are being stored as nvarchar. Not a very good practise, I would say.

Anyway.

Because value stored in [Expirty] column is not a valid date, I will have to assume that it is First of every month. Calculating last day of given month in column would be complicated if not impossible.

I propose following:

select *
from [dbo].[purchase_details]
where convert(date, '01-' + [Expiry]) between convert(date, '21-Dec-2021') and convert(date, '31-Oct-2022')

Hope it helps!
+2
Srinivasan Ramamoorthi4y ago
I recommend changing the expiry column to datetime, then you can have queries that can return results with considering time too. Also converting from char to date might have performance issues.
+1
Sachin Singh4y ago
To Know expiry before 15 days would be good and much better than Now.
select * from PURCHASE_DETAILS PD where PD.EXPIRY = CONVERT(DATE, DATEADD(DAY, 15, GETDATE()));
+1
Satya Karki4y ago
Hi, you can check by comparing the expiry date with the now date.
SELECT * FROM PURCHASE_DETAILS WHERE EXPIRY< NOW() ORDER BY EXPIRY
+1
Rajanikant Hawaldar4y ago
1. SELECT * FROM PURCHASE_DETAILS
2. WHERE EXPIRY < NOW()
3. ORDER BY EXPIRY ASC
Check it.
+1