Can we select row by DATEPART() in MySQL? Is it possible?

MySQLMySQLi Database

There is no DATEPART() function in MySQL, you need to use MONTH() function to extract the month name from date column. The syntax is as follows:

SELECT *FROM yourTableName WHERE MONTH(yourDateColumnName)=yourValue;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table SelectRowFromDatePart
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,  
   -> LoginDate date,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (2.42 sec)

Now you can insert some records in the table using insert command. The query is as follows:

mysql> insert into SelectRowFromDatePart(LoginDate) values(curdate());
Query OK, 1 row affected (0.21 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values(date_add(curdate(),interval -3 year));
Query OK, 1 row affected (0.19 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2014-05-21');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2015-08-25');
Query OK, 1 row affected (0.23 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2018-12-31');
Query OK, 1 row affected (0.18 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2011-12-21');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2013-10-02');
Query OK, 1 row affected (0.12 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2006-12-11');
Query OK, 1 row affected (0.17 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2010-03-09');
Query OK, 1 row affected (0.40 sec)
mysql> insert into SelectRowFromDatePart(LoginDate) values('2012-12-12');
Query OK, 1 row affected (0.70 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from SelectRowFromDatePart;

The following is the output:

+----+------------+
| Id | LoginDate  |
+----+------------+
|  1 | 2019-01-31 |
|  2 | 2016-01-31 |
|  3 | 2014-05-21 |
|  4 | 2015-08-25 |
|  5 | 2018-12-31 |
|  6 | 2011-12-21 |
|  7 | 2013-10-02 |
|  8 | 2006-12-11 |
|  9 | 2010-03-09 |
| 10 | 2012-12-12 |
+----+------------+
10 rows in set (0.00 sec)

Here is the query to select row by datepart, which is MONTH():

mysql> select *from SelectRowFromDatePart where MONTH(LoginDate)=12;

The following is the output:

+----+------------+
| Id | LoginDate  |
+----+------------+
|  5 | 2018-12-31 |
|  6 | 2011-12-21 |
|  8 | 2006-12-11 |
| 10 | 2012-12-12 |
+----+------------+
4 rows in set (0.00 sec)
raja
Published on 27-Feb-2019 06:51:05
Advertisements