MySQL – Queries on Date Manipulation
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.
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.
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;
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:
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';
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.