CODEWITHSUNDEEP
mysqldate
Mohamad
Mohamad

Reputation: 35349

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

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

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?

Upvotes: 14

Views: 10421

Answers (3)

Larry Watanabe
Larry Watanabe

Reputation: 10184

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.

Upvotes: 5

pickypg
pickypg

Reputation: 22332

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.

Upvotes: 16

Jason S
Jason S

Reputation: 189836

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'.

Upvotes: 12

Related Questions