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)
Published on 10-Jan-2019 10:00:42
