I have a table with expiration CB cards dates in format MMYY (example: 0322). I want to query cards which will expire in the next month.
I know about DATE_FORMAT, but when I try this query:
SELECT * FROM payment_way WHERE expiration_date <= DATE_FORMAT(NOW(), "%m%y")
I have cards with expiration_date like: 0523
How can I select expiration dates for the next month? I thought I had a good way with DATE_FORMAT.
You can use:
where expiration_date = date_format(now() + interval 1 month, '%m%y')
You should also fix the expiration date so it is stored using a more standard format. At least YYYYMM, if not a proper date.
If you want either this month or next month, I would suggest:
where expiration_date in (date_format(now(), '%m%y'), date_format(now() + interval 1 month, '%m%y'))
Both of these are index-safe, so they can use an index on your column.