Member Avatar for ItsMe5247

I have a table called Report Card Table. In that table there is colum called expiry date. I would like to know everybody who's card is expring in less than or equal to 90 days

Query.JPG 22.24 KB Query.JPG 22.24 KB
  • 3 Contributors
  • 5 Replies
  • 354 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

Both and the replies from and are on the right track — you need a date-range that includes past (already expired) rows plus any expiry within the next 90 days. A few practical points before the query: use INTERVAL 90 DAY (three calendar months can be 89–92 days), decide whether your column is DATE or DATETIME (that changes how to handle end-of-day inclusivity), and make sure the column name is consistent (expirydate vs expiry_date).

Example (compact, readable output — days left and a status column). This keeps the range test on the column (so an index can be used) and uses DATEDIFF only in the SELECT for clarity:

SELECT id, card_holder, expirydate,
       DATEDIFF(expirydate, CURDATE()) AS days_left,
       CASE
         WHEN expirydate < CURDATE() THEN 'expired'
         WHEN expirydate < DATE_ADD(CURDATE(), INTERVAL 91 DAY) THEN 'expiring_within_90_days'
         ELSE 'ok'
       END AS status
FROM report_card_table
WHERE expirydate IS NOT NULL
  AND expirydate < DATE_ADD(CURDATE(), INTERVAL 91 DAY)
ORDER BY expirydate;

Notes and troubleshooting

  • If expirydate is a DATE (no time part) you can use <= DATE_ADD(CURDATE(), INTERVAL 90 DAY) instead; if it’s DATETIME and you want the whole 90th day included, use < DATE_ADD(CURDATE(), INTERVAL 91 DAY) as shown.
  • Avoid wrapping expirydate in functions in the WHERE clause (e.g., DATE(expirydate)) because that prevents index use. Add an index on expirydate for fast range scans.
  • If values are stored as text, convert them to DATE/DATETIME or use a one-time migration; string comparisons are unreliable.
  • Test boundary cases (today, exactly 90 days out) and server timezone settings if results look off.

This keeps the logic clear, covers expired + expiring, and is simple to adapt into a view or scheduled report.

Recommended Answers

All 5 Replies

Member Avatar for urtrivedi 
SELECT *  FROM `report_card_table` WHERE expirydate>=current_date() and expiry_date<=date_add( current_date(),interval 3 month)
Member Avatar for peeyush.budhia

Instead of using comparison operator (>=) and logical operator (AND) you can use the BETWEEN operator. BETWEEN operator process the query fast as compare to comparison operator and logical operator. Use the following query to get your result.

SELECT * FROM report_card_table WHERE expirydate BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(),INTERVAL 3 MONTH);

Member Avatar for ItsMe5247

I want also show the already expired once.

Member Avatar for ItsMe5247

I want also show the already expired once along with expering in 90 days.

Member Avatar for urtrivedi 
SELECT *  FROM `report_card_table` WHERE expirydate <=  date_add( current_date(),interval 3 month)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.