MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

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 typeDescription
DATEA 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.
YEARA 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 query 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 FORMAT 'YYYY-MM-DD'
);

This will create a empty table named Employee containing five columns as shown below:

EmpIDNameCityAgeDate of Joining

Example: Working with date datatype

Consider a database table called Employee with the following records:

EmpIDNameCityAgeDate of Joining
1JohnLondon252019-05-25
2MarryNew York242018-10-15
3JoParis272017-06-09
4KimAmsterdam302014-04-21
5RameshNew Delhi282019-05-25
6HuangBeijing282020-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:

EmpIDNameCityAgeDate of Joining
1JohnLondon252019-05-25
5RameshNew Delhi282019-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.


5