How do I store a credit card's expiration date if it only consists of the month and year?

Question

The date field in mysql accepts the following format: yyyy-mm-dd. An expiration date without a day is not invalid using that format.

Do I use varchar, instead? And does that not prevent me from making calculations to determine when cards expire and what not?

Is it _always_ only a month and a year? (I know some cards have a day printed on them, but do you need to store the day?) — Random832, Apr 17 '11 at 19:17
You are aware that storing credit card data is a pretty complex field, regulated even by law e.g. in the United States? See for example http://stackoverflow.com/questions/2240705/online-credit-card-storage — Pekka, Apr 17 '11 at 19:18
And related to storing credit card data in a database http://stackoverflow.com/questions/3328922/saving-credit-card-information-in-mysql-database — Pekka, Apr 17 '11 at 19:19
@Pekka, I am aware, but unfortunately I don't have a choice. Client needs to do monthly recurrent billing and outsourcing is too expensive (not based in the US). There's an SSL installed on the site, and all card numbers and ccv numbers are encrypted using AES. — Mohamad, Apr 17 '11 at 19:37

score 15 · Answer 1 · answered Apr 17 '11 at 19:20

15

You could always just use the LAST_DAY function to insert the last day of the month.

For example:

SELECT LAST_DAY('2011-02-01')

Would result in 2011-02-28 this year. In general, you want the last day of the month for a credit card because that's the actual last day it's valid.

answered Apr 17 '11 at 19:20

pickypg

22,034
5
72
84

+1 I like this answer over the other two as it requires only one calculation when the value is saved and then one can compare this value with any date to see if the card has expired. – ypercubeᵀᴹ Apr 17 '11 at 19:44
1

@ypercube: (a) you have to be careful about time of day -- really should be comparing against the 1st day of the next month, and (b) the retailer shouldn't be the one determining at what instant the card expires; that's the authority of the credit card company. – Jason S Apr 17 '11 at 21:01
@Jason: I agree 100% percent with all you said. Even with storing the first day of the month, noone can be sure for expiration. Who knows what timezone does the credit card company uses? – ypercubeᵀᴹ Apr 17 '11 at 21:08
@Jason S: you're right that the retailer should try the credit card anyway, but if it fails, it would be nice to say "Your credit card is failing: It looks like it might have expired." – Neil G Apr 17 '11 at 22:31

score 12 · Accepted Answer · answered Apr 17 '11 at 19:18

For Jan 2014:

2014-01-00

It's common to use 00 for month and day that are undefined. From the MySQL reference manual:

Some date functions can be used with “zero” dates or incomplete dates such as '2001-11-00', whereas others cannot. Functions that extract parts of dates typically work with incomplete dates and thus can return 0 when you might otherwise expect a nonzero value. For example:
  mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
      -> 0, 0

and later on in the manual for DATE_FORMAT:

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'.

score 5 · Answer 3 · edited Apr 17 '11 at 21:00

5

Just use the first day of the month when storing in the standard date format. It's handy to have the date format for conversions, comparisons, etc.

edited Apr 17 '11 at 21:00

Ankur Banerjee

768
1
11
24

answered Apr 17 '11 at 19:16

Larry Watanabe

10,126
9
43
46

But the first day of the month and the month itself are two different concepts. – Jason S Apr 17 '11 at 19:22
Moreover, when a expiration card date is set to, for example, March of 2015, when we are at March 15th, 2014, it's still a valid card. If we set the first day of the month and then we make a comparation, March 15th is greater than March 1st, so it won't be valid to the comparation query. I'd rather put the 1st day of the month and add a month to the date (so March 2015 would become April 1st, 2015). However, this way you lose the original expiration date (which can be recalculated knowing that you have only added one month). – Unapedra Nov 17 '14 at 14:21

How do I store a credit card's expiration date if it only consists of the month and year?

3 Answers3