Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

MySQL Date Data Type

  • Last Updated : 15 Feb, 2021

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’. 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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 ClassContact_Details
1AmitJana2004-12-22XI1234567890
2ManikAggarwal2006-07-04IX1245678998
3NitinDas2005-03-14X2245664909
4PriyaPal2007-07-24VIII3245642199
5BiswanathSharma2005-11-11X2456789761
6ManiPunia2006-01-20IX3245675421
7PritamPatel2008-01-04VII3453415421
8SayakSharma2007-05-10VIII1214657890

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 :

PRODUCTIDPRODUCTNAMEMANUFACTURED_ON
11001ASUS 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 :

ORDERNUMBERORDERDATESHIPPEDDATEDELIVERYDATE
10012019-12-212004-12-222019-12-26
10022020-01-212020-01-212020-01-22
10032020-05-012020-05-032020-05-10
10042020-07-312020-08-012020-08-01
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!