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

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

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)
Updated on: 2019-07-30T22:30:24+05:30

844 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements