MySQL Date Data Type :
There are various data types that are supported in MySQL. Among them sometimes we need to take DATE data type to store data values. The DATE type is used for values with a date part but no time part. It displays DATE values in ‘YYYY-MM-DD’ format. We can store any date value which is in the given range ‘1000-01-01’ to ‘9999-12-31’.
Syntax :
Variable_Name DATE
The following examples will illustrate how we can use Date data type in a variable.
Example 1 :
Creating a StudentDetails table –
It consists of Student_Id, First_name, Last_name, Date_Of_Birth, Class, Contact_Details columns. Among which the data type of Date_Of_Birth column is DATE.
CREATE TABLE StudentDetails (
Student_Id INT AUTO_INCREMENT,
First_name VARCHAR (100) NOT NULL,
Last_name VARCHAR (100) NOT NULL,
Date_Of_Birth DATE NOT NULL,
Class VARCHAR (10) NOT NULL,
Contact_Details BIGINT NOT NULL,
PRIMARY KEY(Student_Id )
);
Inserting data into the Table –
INSERT INTO
StudentDetails(First_name , Last_name , Date_Of_Birth , Class, Contact_Details)
VALUES
('Amit', 'Jana', '2004-12-22', 'XI', 1234567890),
('Manik', 'Aggarwal', '2006-07-04', 'IX', 1245678998),
('Nitin', 'Das', '2005-03-14', 'X', 2245664909),
('Priya', 'Pal', '2007-07-24', 'VIII', 3245642199),
('Biswanath', 'Sharma', '2005-11-11', 'X', 2456789761),
('Mani', 'Punia', '2006-01-20', 'IX', 3245675421),
('Pritam', 'Patel', '2008-01-04', 'VII', 3453415421),
('Sayak', 'Sharma', '2007-05-10', 'VIII' , 1214657890);
To verify using the following command as follows.
SELECT * FROM StudentDetails ;
Output :
Student_Id |
First_name |
Last_name |
Date_Of_Birth |
Class |
Contact_Details |
1 |
Amit |
Jana |
2004-12-22 |
XI |
1234567890 |
2 |
Manik |
Aggarwal |
2006-07-04 |
IX |
1245678998 |
3 |
Nitin |
Das |
2005-03-14 |
X |
2245664909 |
4 |
Priya |
Pal |
2007-07-24 |
VIII |
3245642199 |
5 |
Biswanath |
Sharma |
2005-11-11 |
X |
2456789761 |
6 |
Mani |
Punia |
2006-01-20 |
IX |
3245675421 |
7 |
Pritam |
Patel |
2008-01-04 |
VII |
3453415421 |
8 |
Sayak |
Sharma |
2007-05-10 |
VIII |
1214657890 |
So, we have successfully stored the DATE data-type in the Date_Of_Birth Column.
Example 2 :
Creating a ProductDetails table –
It consists of ProductId, ProductName, and Manufactured_On columns, among which the data type for Manufactured_On columns is DATE.
CREATE TABLE ProductDetails(
ProductId INT NOT NULL,
ProductName VARCHAR(20) NOT NULL,
Manufactured_On DATE NOT NULL,
PRIMARY KEY(ProductId)
);
Inserting data into the Table –
The CURRENTDATE function is used to assign value in the Manufactured_On column. The return data type for CURRENTDATE function is DATE.
INSERT INTO
ProductDetails(ProductId, ProductName, Manufactured_On)
VALUES
(11001, 'ASUS X554L', CURRENT_DATE()) ;
To verify using the following command as follows.
SELECT * from ProductDetails;
Output :
PRODUCTID |
PRODUCTNAME |
MANUFACTURED_ON |
11001 |
ASUS X554L |
2020-12-08 |
Example 3 :
Creating an orders table –
It consists of OrderNumber, OrderDate, ShippedDate, DeliveryDate columns. Among which the data type of OrderDate, ShippedDate, and DeliveryDate column is DATE.
CREATE TABLE Orders(
OrderNumber INT AUTO_INCREMENT,
OrderDate DATE NOT NULL,
ShippedDate DATE NOT NULL,
DeliveryDate DATE NOT NULL,
PRIMARY KEY(OrderNumber )
);
Inserting data into the Table –
INSERT INTO
Orders(OrderNumber , OrderDate , ShippedDate , DeliveryDate )
VALUES
(1001, '2019-12-21', '2004-12-22', '2019-12-26'),
(1002, '2020-01-21', '2020-01-21', '2020-01-22'),
(1003, '2020-05-01', '2020-05-03', '2020-05-10'),
(1004, '2020-07-31', '2020-08-01', '2020-08-01');
To verify used the following command as follows.
SELECT * FROM Orders;
Output :
ORDERNUMBER |
ORDERDATE |
SHIPPEDDATE |
DELIVERYDATE |
1001 |
2019-12-21 |
2004-12-22 |
2019-12-26 |
1002 |
2020-01-21 |
2020-01-21 |
2020-01-22 |
1003 |
2020-05-01 |
2020-05-03 |
2020-05-10 |
1004 |
2020-07-31 |
2020-08-01 |
2020-08-01 |
Share your thoughts in the comments
Please Login to comment...