MySQL datatype to store month and year only?

MySQLMySQLi Database

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