Open In App

MySQL – Queries on Date Manipulation

Improve
Improve
Like Article
Like
Save
Share
Report

In MySQL date is a data type and by default follows the format ‘YYYY-MM-DD’ but by using date formatting functions we can format date and can get the required format of date in the tables and can even manipulate the date by using different date functions.

In this article let us execute some queries on date formatting in detail step-by-step:

Creating a database :

Creating a database student by using the following SQL query as follows.

CREATE DATABASE orders;

Using the database :

Using the database student using the following SQL query as follows.

USE orders;

Creating a table :

Table –Creating a table order_details with 3 columns using the following SQL query as follows.

CREATE TABLE order_details(
order_id varchar(8),
customer_name varchar(20),
order_date DATE);

 Verifying the database :

To view the description of the tables in the database using the following SQL query as follows.

 DESCRIBE order_details;

Inserting data into the table :
 

Inserting rows into order_details table using the following SQL query as follows.

 INSERT INTO order_details VALUES
     ('O0001','PRADEEP','2021-04-11'),
     ('O0002','KOUSHIK','2021-04-09'),
     ('O0003','SRINIVAS','2021-04-05'),
     ('O0004','SATWIK','2021-04-08'),
     ('O0005','LOKESH','2021-04-03');

Verifying the inserted data :
 

Viewing the table order_details after inserting rows by using the following SQL query as follows.

 SELECT* FROM order_details;

QUERIES :

1.Query to format order_date as ‘DD-MM-YY’.

The default date can be formatted from the default date by using the DATE_FORMAT( ) function:

SYNTAX:
DATE_FORMAT(date,new_format);

In this query, we are using %d to get day then %b to get abbreviated month and %y to get the year.

 SELECT *,DATE_FORMAT(order_date,'%d-%b-%y') AS formatted_date
 FROM order_details;

2. Query to get the estimated delivery date after 15 days from the order date.

The days required can be added by using the DATE_ADD( ) function:

SYNTAX:
DATE_ADD(date, INTERVAL no_of_days DAY);
 SELECT *,DATE_ADD(order_date,INTERVAL 15 DAY)
 AS estimated_delivery
 FROM order_details;

3.Query to get the estimated delivery day.

This can be done by using both DATE_FORMAT( ) and DATE_ADD( ) functions.

 SELECT *,DATE_FORMAT(DATE_ADD(order_date,INTERVAL 15 DAY),'%W')
 AS delivery_day
 FROM order_details;

Here %W to format the date as a weekday.

4. Query to get the order_id and customer name of the persons who are getting their order delivered before  2021-04-23.

Here DATE_ADD( ) function is used to get the estimated delivery and check if it is less than 2021-04-23.

 SELECT order_id,customer_name
   FROM order_details
   WHERE DATE_ADD(order_date,INTERVAL 15 DAY)<'2021-04-23';


Last Updated : 09 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads