MySQL Select Date Equal to Today and return results for the same date?

MySQLMySQLi Database

To get today’s date, use in-built function CURDATE(). The CURDATE() gives only current date not time. With that, to get the records for the same day, you can try the following syntax −

select yourColumnName1,yourColumnName2,......,yourColumnNameN,DATE_FORMAT(yourDateColumnName, '%Y-%m-%d') from yourTableName
WHERE DATE(yourDateColumnName) = CURDATE();

To understand the above concept, let us create a table. The query to create a table is as follows. One of these columns will have datetime datatype to display dates −

mysql> create table GmailSignIn
   −> (
   −> UserId int,
   −> UserName varchar(200),
   −> DateOfSignIn datetime
   −> );
Query OK, 0 rows affected (1.56 sec)

Now you can insert some records into the table with the help of insert command. We have set the current date as well, which is 2018-12-06.

The query is as follows −

mysql> insert into GmailSignIn values(222111,'John',now());
Query OK, 1 row affected (0.15 sec)

mysql> insert into GmailSignIn values(333222,'Johnson',curdate());
Query OK, 1 row affected (0.18 sec)

mysql> insert into GmailSignIn values(444333,'Carol',date_add(curdate(),interval 1 day));
Query OK, 1 row affected (0.13 sec)

mysql> insert into GmailSignIn values(555444,'David',date_add(curdate(),interval -1 day));
Query OK, 1 row affected (0.83 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from GmailSignIn;

The following is the output −

+--------+----------+---------------------+
| UserId | UserName | DateOfSignIn        |
+--------+----------+---------------------+
| 222111 | John     | 2018-12-06 19:13:30 |
| 333222 | Johnson  | 2018-12-06 00:00:00 |
| 444333 | Carol    | 2018-12-07 00:00:00 |
| 555444 | David    | 2018-12-05 00:00:00 |
+--------+----------+---------------------+
4 rows in set (0.00 sec)

Here is the query to select date equal to today and display the records for the same date −

mysql> select UserId,UserName,DateOfSignIn,DATE_FORMAT(DateOfSignIn, '%Y-%m-%d') from GmailSignIn
   −> where date(DateOfSignIn) = curdate();

The following is the output −

+--------+----------+---------------------+---------------------------------------+
| UserId | UserName | DateOfSignIn        | DATE_FORMAT(DateOfSignIn, '%Y-%m-%d') |
+--------+----------+---------------------+---------------------------------------+
| 222111 | John     | 2018-12-06 19:13:30 | 2018-12-06                            |
| 333222 | Johnson  | 2018-12-06 00:00:00 | 2018-12-06                            |
+--------+----------+---------------------+---------------------------------------+
2 rows in set (0.00 sec)
raja
Published on 11-Jan-2019 08:06:51
Advertisements