MySQL datatype to store month and year only?

You need to store the date as the complete date time rather than storing only month and year. If you declare as a datetime then you can extract the month and year using MONTH() and YEAR() function from MySQL.

The syntax is as follows −

select MONTH(yourDateTimeColumnName) as anyVariableName1, YEAR(yourDateTimeColumnName) as anyVariableName2 from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table OnlyMonthandYear    -> (       -> DueDateTime datetime    -> ); Query OK, 0 rows affected (0.56 sec)

Insert date in the table using insert command. The query to insert record is as follows −

mysql> insert into OnlyMonthandYear values('2016-12-10'); Query OK, 1 row affected (0.19 sec) mysql> insert into OnlyMonthandYear values('2017-10-21'); Query OK, 1 row affected (0.15 sec) mysql> insert into OnlyMonthandYear values('2018-03-25'); Query OK, 1 row affected (0.31 sec) mysql> insert into OnlyMonthandYear values('2018-12-18'); Query OK, 1 row affected (0.20 sec)

Display all records from table using select command. The query is as follows −

mysql> select *from OnlyMonthandYear;

+---------------------+ | DueDateTime         | +---------------------+ | 2016-12-10 00:00:00 | | 2017-10-21 00:00:00 | | 2018-03-25 00:00:00 | | 2018-12-18 00:00:00 | +---------------------+ 4 rows in set (0.00 sec)

Here is the query that extracts the month and year only from datetime datatype column.

The query is as follows −

mysql> select Month(DueDateTime) as OnlyMonth,Year(DueDateTime) as OnlyYear from OnlyMonthandYear;

Output

+-----------+----------+ | OnlyMonth | OnlyYear | +-----------+----------+ |        12 |     2016 | |        10 |     2017 | |         3 |     2018 | |        12 |     2018 | +-----------+----------+ 4 rows in set (0.02 sec)