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)