Ex No: 16 Working with Date and Time
Aim: To demonstrate the Date and Time Operations available in MYSQL.
Procedure
a) Table Creation - Create Orders table with details such as order id, quantity, order date, order time, order delivery date and amount.
b) Table Creation - Create order item table with order id, product name, quantity and price.
c) Insert required records in Orders and Order item table.
d) Display Order details.
e) Display orders placed between two dates.
f) Display order delivery day for all orders.
g) Display orders with delivery within 7 days.
h) Display orders placed in the month of JANUARY.
i) Display orders placed between 6.00 PM and 10.00 PM.
Query
a) Employee Table Creation 
 CREATE TABLE ORDERS(
 ORDERNO INT PRIMARY KEY AUTO_INCREMENT,
 QTY INT,
 ORDERDATE DATE NOT NULL,
 ORDERTIME TIME NOT NULL,
 ORDERDEL DATE NOT NULL,
 AMOUNT INT)
 AUTO_INCREMENT=1000;
b) Working Table Creation 
CREATE TABLE ORDERITEM(
ORDERNO INT NOT NULL,
PRODNAME VARCHAR(30),
QTY INT,
PRICE INT);
c) Insert 10 Orders in the database
First Order 
INSERT INTO ORDERS(QTY,ORDERDATE,ORDERTIME,ORDERDEL, AMOUNT) 
VALUES (3,'2018-01-13',CURTIME(),DATE_ADD('2018-01-13',INTERVAL 5 DAY),1200);

INSERT INTO ORDERITEM(ORDERNO,PRODNAME,QTY,PRICE)
VALUES (LAST_INSERT_ID(),'MOTO G SCREEN GUARD',2,1000),
(LAST_INSERT_ID(),'MOTO G FLIP CASE',1,200);
Second Order 
INSERT INTO ORDERS(QTY,ORDERDATE,ORDERTIME,ORDERDEL, AMOUNT) 
VALUES (1,'2018-02-18','18:10:23',DATE_ADD('2018-02-18',INTERVAL 5 DAY),18000);

INSERT INTO ORDERITEM(ORDERNO,PRODNAME,QTY,PRICE)
VALUES (LAST_INSERT_ID(),'NOKIA 5',1,18000);
d) Query to Display Order details 
SELECT ORD.ORDERNO, ORD.ORDERDATE, ORD.ORDERDEL, ITM.PRODNAME, ITM.QTY, ITM.PRICE
FROM ORDERS ORD, ORDERITEM ITM
WHERE ORD.ORDERNO = ITM.ORDERNO;
e) Query to Display orders placed between two dates 
SELECT ORD.ORDERNO, ITM.PRODNAME, ITM.QTY, ITM.PRICE
FROM ORDERS ORD, ORDERITEM ITM
WHERE ORD.ORDERNO = ITM.ORDERNO and ORD.ORDERDATE BETWEEN '2018-02-12' AND '2018-02-22';
f) Query to Display Order delivery day for all orders 
SELECT ORD.ORDERNO, DAYNAME(ORD.ORDERDEL) DAYNAME, ITM.PRODNAME, ITM.QTY, ITM.PRICE
FROM ORDERS ORD, ORDERITEM ITM
WHERE ORD.ORDERNO = ITM.ORDERNO;
g) Query to Display Order delivery within 7 days 
 SELECT ORD.ORDERNO, ITM.PRODNAME, ITM.QTY, ITM.PRICE
 FROM ORDERS ORD, ORDERITEM ITM
 WHERE ORD.ORDERNO = ITM.ORDERNO AND DATEDIFF(ORDERDEL,ORDERDATE)<7;
h) Query to Display Orders placed in month of JANUARY 
SELECT ORD.ORDERNO, ITM.PRODNAME, ITM.QTY, ITM.PRICE
FROM ORDERS ORD, ORDERITEM ITM
WHERE ORD.ORDERNO = ITM.ORDERNO AND MONTHNAME(ORDERDATE)='JANUARY';
i) Query to Display placed in the evening (6.00PM to 10.00PM) 
 SELECT ORD.ORDERNO, ORD.ORDERTIME, ITM.PRODNAME, ITM.QTY, ITM.PRICE
 FROM ORDERS ORD, ORDERITEM ITM
 WHERE ORD.ORDERNO=ITM.ORDERNO AND HOUR(ORD.ORDERTIME) BETWEEN 18 AND 22;
Result: Thus Date and Time Manipulation functions has been performed using MYSQL queries.