- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- Do a select in MySQL based only on month and year?
- Group month and year in MySQL?
- Format MySQL date and convert to year-month-day
- MySQL query to fetch date with year and month?
- Which MySQL datatype to used to store an IP address?
- How to select month and year from dates in MySQL?
- What is the MySQL datatype to store DATALINK object in JDBC
- 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?
- Extract the month and year in the following format: “mm-yyyy” (month year) along with all columns in MySQL?
- MySQL query to get result by month and year based on condition?
- MySQL query to update only month in date?
- Filter the records of current day, month and year in MySQL?
- How to compare Year, Month and Day in a MySQL query and display matching records

Advertisements