MySQL - DATES

While working with MySQL Date datatypes, it is essential to know about the dates format when performing different operations on a database. Please note that, the datatype of column must be chosen while creating a new table in the database.

The different data types available in MySQL for storing a date or a date/time value are mentioned below:

Data type	Description
DATE	A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'.
DATETIME(fsp)	A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time.
TIMESTAMP(fsp)	A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition.
YEAR	A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format.

Example: Create a table with date datatype

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Date_of_Joining.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Date_of_Joining DATE
);

This will create a empty table named Employee containing five columns.

Example: Working with date datatype

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Date_of_Joining
1	John	London	25	2019-05-25
2	Marry	New York	24	2018-10-15
3	Jo	Paris	27	2017-06-09
4	Kim	Amsterdam	30	2014-04-21
5	Ramesh	New Delhi	28	2019-05-25
6	Huang	Beijing	28	2020-01-10

To fetch data of all employees present in the Employee table who joined the organization on '2019-05-25', the query is:

SELECT * FROM Employee
WHERE Date_of_Joining = '2019-05-25';

This will produce the result as shown below:

EmpID	Name	City	Age	Date_of_Joining
1	John	London	25	2019-05-25
5	Ramesh	New Delhi	28	2019-05-25

Complete MySQL Date and Time Reference

For a complete reference of all MySQL Date and Time functions, see the MySQL Data and Time Reference.