Open In App

MySQL DATE Data Type

Last Updated : 17 May, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

MySQL DATE Data Type stores date values in the format ‘YYYY-MM-DD‘ and has a valid range of values from ‘1000-01-01‘ to ‘9999-12-31‘.

DATE Data Type in MySQL

The Data data type in MySQL is used to store date values in a column. During later data analysis, it is necessary to perform date-time operations on the data.

Dates are displayed in the format ‘YYYY-MM-DD’, but can be inserted using either strings or numbers. If any invalid date is inserted, MySQL will store ‘0000-00-00’ by default.

Syntax

DATE Data Type Syntax is:

Variable_Name DATE

MySQL DATE Data Type Examples

The following examples will illustrate how we can use Date data type in MySQL in a variable.

Creating a table with DATE Data Type Column Example

In this example, we will create a table ‘StudentDetails’. The table consists of Student_Id, First_name, Last_name, Date_Of_Birth, Class, Contact_Details columns. Among these the data type of Date_Of_Birth column is DATE.

Query:

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 Date into DATE Data Type Column Example

In this example, we will insert data into table. We will also insert date in the Date_Of_Birth column as its data type is Date.

Query:

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.

Similary we can create another table ‘ProductDetails’

It consists of ProductId, ProductName, and Manufactured_On  columns, among which the data type for Manufactured_On columns is DATE.

Query:

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.

Query:

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

Previous Article
Next Article

Similar Reads

Load .CSV Data into MySQL and Combine Date and Time
Prerequisite - How To Import Timestamp From a CSV File in MySQL? A CSV (comma-separated values) file is a text file with commas separating information. They are most commonly found in spreadsheets and databases and are now primarily used for datasets in data science and machine learning. They assist businesses in exporting large amounts of data to
4 min read
MySQL | Common MySQL Queries
MySQL server is a open-source relational database management system which is a major support for web based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depends on MySQL dat
9 min read
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: Creatin
2 min read
DATE() in MySQL
The DATE() function is used to extract the date part from a DateTime expression. This function is an inbuilt function in MySQL. The DATE() function in MySQL can be used to know the date for a given date or a DateTime. The DATE() function takes a date value as an argument and returns the date. The date argument represents the valid date or DateTime.
1 min read
Various String, Numeric, and Date & Time functions in MySQL
A function is a special type of predefined command set that performs some operation and returns a single value. Functions operate on zero, one, two or more values that are provided to them. The values that are provided to functions are called parameters or arguments. The MySQL functions have been categorized into various categories, such as String
3 min read
How to Convert MySQL Table Field Type from BLOB to JSON?
In this article, we would be learning a MySQL query to convert a field of BLOB Data Type to JSON Data Type in a table. To execute this query, we would need to alter the table and subsequently the field's definition. We would first need to use the ALTER TABLE command to start making changes to the table. ALTER TABLE: ALTER TABLE is a command used to
2 min read
How to Write a SQL Query For a Specific Date Range and Date Time?
In SQL, some problems require us to retrieve rows based on their dates and times. For such cases, we use the DATETIME2 datatype present in SQL. For this article, we will be using the Microsoft SQL Server as our database. Note - Here, we will use the WHERE and BETWEEN clauses along with the query to limit our rows to the given time. The pattern of s
2 min read
How to convert a value of one type to another type in SQL server
Convert means to change the form or value of something. The CONVERT() function in SQL server is used to convert a value of one type to another type. Syntax : SELECT CONVERT ( target_type ( length ), expression ) Parameters used : target_type - It is the target data type to which the to expression will be converted, e.g: INT, BIT, SQL_VARIANT, etc.
2 min read
Java Servlet and JDBC Example | Insert data in MySQL
Prerequisites: Servlet, JDBC Connectivity To start with interfacing Java Servlet Program with JDBC Connection: Proper JDBC Environment should set-up along with database creation. To do so, download the mysql-connector.jar file from the internet, As it is downloaded, move the jar file to the apache-tomcat server folder, Place the file in lib folder
4 min read
How to Import and Export Data to Database in MySQL Workbench?
Utilizing MySQL Workbench, a unified visual database designing or graphical user interface tool is necessary when working with database architects, developers, and administrators. It was made and is updated by Oracle. It offers comprehensive administration tools for server configuration, user administration, backup, and many other tasks as well as
3 min read
Article Tags :