mysql datatype to store month and year only

It says in the MySQL manual that you can store partial dates

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_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'.

This means that to store the year and month only, you can use a DATE column and put 00 instead of the day. e.g 2013-12-00.

Related: Store incomplete date in MySQL date field

Consider how you are going to use the data. If there are any reports you have to create, which way would allow you to retrieve and work with the data more easily?

And you don't have to use a date type field. You could just have a Year field and a Month field that are both integers. Then when you actually need to do any kind of expression with it requiring a date it's easy enough to put them together and cast to a date.

And storing as a date with the day number as 1 and just ignoring it is perfectly okay and fairly normal too. In the end this isn't a decision that's going to matter a whole lot (relatively speaking) so I would just choose the one you like best and get it done.

Why bother? Just store it as a complete date (perhaps always using the first as the day) and use the database functions MONTH() and YEAR() if you only need part of it. This makes using that field much easier as you can still do range queries, etc.

Another solution is to build generated columns from your DATETIME/DATE source.

ALTER TABLE stats 
ADD COLUMN year SMALLINT GENERATED ALWAYS AS (YEAR(stat_date)) NOT NULL,
ADD COLUMN month smallint GENERATED ALWAYS AS (MONTH(stat_date)) NOT NULL;

Background

I had a similar problem. After reading this thread, I decided to store incomplete dates (with zeros). It worked on older versions of MySQL, but newer versions produced "Incorrect date" error. As mentioned before, you can turn the error into warning using the NO_ZERO_IN_DATE setting. But the setting itself is deprecated. Hence, in the future, it would only be possible to support zeros in dates by disabling the strict mode, thus, silencing other types of errors.

Because NO_ZERO_IN_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

My requirement was to build a monthly view of a table. I had to add an index on month. So, computing moth on-the-fly with YEAR() and MONTH() was not an option.

Generated columns served their purpose. The year and month columns weren't part of the primary index, and I could even save space thanks to the VIRTUAL (not STORED) generated columns.

Links

• https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date
• https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html