Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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
Do a select in MySQL based only on month and year?
To select MySQL based on month and year, use in-built function YEAR() and MONTH(). The syntax is as follows −
select *from yourTableName where YEAR(yourColumnName) = YearValue AND MONTH(yourColumnName) = monthValue;
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table selectDataOnYearandMonthDemo −> ( −> BookId int, −> BookName varchar(100), −> BookDueDate datetime −> ); Query OK, 0 rows affected (0.57 sec)
Now you can insert some records in the table. The query is as follows −
mysql> insert into selectDataOnYearandMonthDemo values(101,'C in Depth',date_add(now(),interval -1 year)); Query OK, 1 row affected (0.50 sec) mysql> insert into selectDataOnYearandMonthDemo values(102,'Let us C',date_add(curdate(),interval 1 year)); Query OK, 1 row affected (0.12 sec) mysql> insert into selectDataOnYearandMonthDemo values(103,'SCJP Java','2018-12-5'); Query OK, 1 row affected (0.12 sec) mysql> insert into selectDataOnYearandMonthDemo values(104,'Introduction to C++','2016-4-5'); Query OK, 1 row affected (0.24 sec)
Display all records which we inserted above. The query to display all records is as follows −
mysql> select *from selectDataOnYearandMonthDemo;
The following is the output −
+--------+--------------------+---------------------+ | BookId | BookName | BookDueDate | +--------+--------------------+---------------------+ | 101 | C in Depth | 2017-12-05 21:44:20 | | 102 | Let us C | 2019-12-05 00:00:00 | | 103 | SCJP Java | 2018-12-05 00:00:00 | | 104 | Introduction to C++| 2016-04-05 00:00:00 | +--------+--------------------+---------------------+ 4 rows in set (0.00 sec)
Here is the query to select year and month. The query is as follows −
mysql> select *from selectDataOnYearandMonthDemo where YEAR(BookDueDate) = 2019 AND MONTH(BookDueDate) = 12;
The following is the output −
+--------+----------+---------------------+ | BookId | BookName | BookDueDate | +--------+----------+---------------------+ | 102 | Let us C | 2019-12-05 00:00:00 | +--------+----------+---------------------+ 1 row in set (0.05 sec)
Updated on: 30-Jul-2019
1K+ Views
Advertisements