SQL Query to Get Yesterday and Tomorrow
Last Updated :
08 Apr, 2021
Queries help the users to interact with a database for creating, insertion, deleting, updating data in a database with different queries.
In this article let us see how to get yesterday and tomorrow with respect to the given dates in the database.
Example –
Given date: 2021-03-23
Yesterday: 2021-03-22 Monday
Tomorrow: 2021-03-24 Wednesday
Creating a database calendar:
CREATE DATABASE calendar;
Using the database calendar
USE calendar;
Creating a Table schedule:
CREATE TABLE schedule
(dates date);
Viewing the description of the table:
DESCRIBE schedule;
Inserting rows into the schedule:
INSERT INTO schedule VALUES('2021-03-23');
INSERT INTO schedule VALUES('2020-08-04');
INSERT INTO schedule VALUES('2021-06-08');
INSERT INTO schedule VALUES('2030-04-04');
INSERT INTO schedule VALUES('2025-09-13');
Viewing the data in the table:
SELECT* FROM schedule;
Query to get the yesterday and tomorrow of current date:
To get the yesterday and tomorrow of the current date we can use the CURRDATE() function in MySQL and subtract 1 from it to get yesterday and add 1 to it to get tomorrow.
SELECT CURDATE(),
DATE_SUB(CURDATE(),INTERVAL 1 DAY) AS yesterday,
DATE_ADD(CURDATE(),INTERVAL 1 DAY) AS tomorrow;
Here we can change the default column header to some other name by using AS.
Example1:
Query to get yesterday and tomorrow of dates in the table:
Syntax:
SELECT CURDATE(),
DATE_SUB(CURDATE(),INTERVAL 1 DAY) AS some_name
DATE_ADD(CURDATE(),INTERVAL 1 DAY) AS some_name;
SELECT dates,
DATE_SUB(dates,INTERVAL 1 DAY) AS yesterday,
DATE_ADD(dates,INTERVAL 1 DAY) AS tomorrow
FROM schedule;
Example2:
Query to get the yesterday and tomorrow dates in the table with weekdays:
Syntax:
SELECT column_name,
DATE_SUB(column_name,INTERVAL 1 DAY) AS some_name,
DATE_ADD(column_name,INTERVAL 1 DAY) AS some_name,
DAYNAME(current_date),
DAYNAME(previous_day),
DAYNAME(next_day);
SELECT dates,
DATE_SUB(dates,INTERVAL 1 DAY) AS yesterday,
DATE_ADD(dates,INTERVAL 1 DAY) AS tomorrow,
DAYNAME(dates) AS weekdayofdate,
DAYNAME(DATE_SUB(dates,INTERVAL 1 DAY)) AS weekdayofYd,
DAYNAME( DATE_ADD(dates,INTERVAL 1 DAY)) AS weekdayofTm
FROM schedule;
Share your thoughts in the comments
Please Login to comment...