CODEWITHSUNDEEP
mysql
Crazy Serb
Crazy Serb

Reputation: 76330

Select all expiring credit card dates from the mmyy column in mySQL

If I have a column called cc_expire in mySQL with entries of the format mmyy, how can I select / query only the records for which the mmyy value equals next month?

So for example, if our current mmyy = 0618, I want to return all the entries of 0718 and higher.

Also, how would I account for the next year, as in mmyy = 1218, as the records returned should be equal to 0119 and higher?

Suggestions?

Upvotes: 3

Views: 796

Answers (2)

Markus
Markus

Reputation: 684

You can build the query using the CURDATE doc, SUBSTRING doc and CONCAT doc.

For your first question, select the parts of the current date that you require:

WHERE cc_expire = CONCAT(SUBSTRING(CURDATE(), 5, 2), SUBSTRING(CURDATE(), 2, 2))

For the second question, the date has to be disassembled and checked in two steps for the current year (month is relevant) and future years.

where SUBSTRING(cc_expire, 2, 2) > SUBSTRING(CURDATE(), 2, 2)  # year is larger
    OR (SUBSTRING(cc_expire, 2, 2) = SUBSTRING(CURDATE(), 2, 2)  # year is equal
        AND SUBSTRING(cc_expire, 0, 2) > SUBSTRING(CURDATE(), 5, 2))

Note that I explicitly disregard the problem that occurs due to the year being only 2 numbers (i.e.\@, 99 < 05). Please check for your assumptions on this problem.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522302

Use STR_TO_DATE to build a bona fide date out of your cc_expire column. We can do this by assuming each date is arbitrarily the first of that month. Then, compare each credit card record against the first of the following month.

SELECT *
FROM yourTable
WHERE
    STR_TO_DATE(CONCAT('01', cc_expire), '%d%m%Y') >=
    DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01');

Demo

Note: This answers your question, but it would much better long term if you would store proper dates for the credit card expiration dates.

Upvotes: 4

Related Questions