How to select yesterday's date in MySQL?

To select yesterday’s date, use the subdate() function from MySQL. The syntax is as follows

select subdate(yourDatetimeColumnName) as anyVariableName from yourTableName;

To understand the above syntax, let us create a table

mysql> create table YesterdayDateDemo
-> (
-> VisitedDateTime datetime
-> );
Query OK, 0 rows affected (0.59 sec)

Let us now insert date in the table using insert command. The query is as follows

mysql> insert into YesterdayDateDemo values(now());
Query OK, 1 row affected (0.15 sec)

mysql> insert into YesterdayDateDemo values('2012-12-26 13:24:35');
Query OK, 1 row affected (0.17 sec)

mysql> insert into YesterdayDateDemo values('2013-10-22 12:20:32');
Query OK, 1 row affected (0.16 sec)

Let us now display all records from the table using select command. The query is as follows

mysql> select *from YesterdayDateDemo;

The following is the output

+---------------------+
| VisitedDateTime |
+---------------------+
| 2018-12-18 14:22:48 |
| 2012-12-26 13:24:35 |
| 2013-10-22 12:20:32 |
+---------------------+
3 rows in set (0.00 sec)

Now here is the query to select yesterday’s date for the dates inserted above using subdate() method from MySQL. The query is as follows

mysql> select subdate(VisitedDateTime,interval 1 day) as YesterdayDate from
YesterdayDateDemo;

The following is the output

+---------------------+
| YesterdayDate |
+---------------------+
| 2018-12-17 14:22:48 |
+---------------------+
1 row in set (0.00 sec)

Ankith Reddy

Updated on: 2019-07-30T22:30:24+05:30

652 Views

Kickstart Your Career

Get certified by completing the course

Get Started