Open In App

MySQL Date Data Type

Improve
Improve
Like Article
Like
Save
Share
Report

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

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