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)

George John

Updated on 29-Jun-2020 08:12:26

• Related Questions & Answers
• Do a select in MySQL based only on month and year?
• Group month and year in MySQL?
• MySQL query to fetch date with year and month?
• Format MySQL date and convert to year-month-day
• How to select month and year from dates in MySQL?
• Which MySQL datatype to used to store an IP address?
• MySQL SELECT query to return records with specific month and year
• MySQL to fetch records based on a specific month and year?
• How MySQL use YEAR data type to store year value in a table?
• MySQL query to get result by month and year based on condition?
• What is the MySQL datatype to store DATALINK object in JDBC
• Sort array by year and month JavaScript
• Filter the records of current day, month and year in MySQL?
• MySQL query to update only month in date?
• Extract the month and year in the following format: “mm-yyyy” (month year) along with all columns in MySQL?