MySQL Date and Date Functions

This tutorial explains MySQL DATE data type and walks you through some of the standard date functions. It will help you use and handle dates more efficiently with MySQL.

MySQL DATE data type

The DATE is a temporal data type for accessing and setting dates by applications. It accepts DATE values only in YYYY-MM-DD format. And MySQL doesn’t allow changing it.

The DATE format has three subfields: Year, Month, and the date value. And, they should appear in the given order. You won’t be able to use something like MM-DD-YYYY or anything of that sort.

Let’s now get into the internals of MySQL date and the date functions that we should know.

How to use DATE data type in MySQL

We can use DATE for many purposes, and it is one of the most commonly used data types. MySQL allows us to use the dates in the following fixed format:

-- MySQL DATE Format
-- YEAR->MONTH->DAY
YYYY-MM-DD

We can’t alter the specified DATE template, but there are other ways to follow a different style. We’ll discuss them in a separate tutorial.

MySQL allocates three bytes to stock a DATE value. And we can use any date falling under the following range:

-- MySQL DATE Range
1000-01-01 <= Acceptable DATE value <= 9999-12-31

Storing DATE with default format

In this MySQL DATE example, we are going to create a table that would have two date fields. Both these columns will use the default DATE format.

One is dateCreated which we would feed into the MySQL INSERT statement. And, another is dateUpdated which takes a default value, i.e., 9999-12-31.

So, let’s first create a tabled named Tutorials. It will have the following schema:

Tutorials
 |__tutorialId (integer)
 |__tutorialName (string)
 |__dateCreated (date)
 |__dateUpdated (date)

Here is the CREATE TABLE command:

-- Creating a table using MySQL DATE type fields
CREATE TABLE Tutorials
    (
        tutorialId INT AUTO_INCREMENT PRIMARY KEY,
        tutorialName VARCHAR(60),
        dateCreated DATE,
        dateUpdated DATE NOT NULL DEFAULT '9999-12-31'
    );

Now, let’s insert the data into the Tutorials table.

-- Inserting some data with default date and given values
INSERT INTO Tutorials
    (
        tutorialName, dateCreated
    )
VALUES
    ('How to Use MySQL Insert Statement', '2019-07-21'),
    ('How to Use MySQL Select Statement', '2019-07-14'),
    ('How to Use MySQL Update Statement', '2019-08-01'),
    ('How to Use MySQL Delete Statement', '2019-08-05');

After feeding the data, let’s fetch the records from the Tutorials table:

-- Print all rows with date values
SELECT 
    *
FROM
    Tutorials;

You can see that the dateCreated fields took the provided date values, whereas the dateUpdated assumed default values.

1	How to Use MySQL Insert Statement	2019-07-21	9999-12-31
2	How to Use MySQL Select Statement	2019-07-14	9999-12-31
3	How to Use MySQL Update Statement	2019-08-01	9999-12-31
4	How to Use MySQL Delete Statement	2019-08-05	9999-12-31

Storing DATE with Year in two digits

MySQL allows double-digit year values in the DATE field. However, it converts them in the following manner:

# MySQL converts two digits year value to four.
YEAR 00-69 => 2000-2069
YEAR 70-99 => 1970-1999

Let’s re-run the previous example while we’ll specify two digits in the year. Since we are not changing the CREATE statement, so will skip it here.

-- Inserting some data with default date and given values
INSERT INTO Tutorials
    (
        tutorialName, dateCreated
    )
VALUES
    ('How to Use MySQL Insert Statement', '69-07-21'),
    ('How to Use MySQL Select Statement', '69-07-14'),
    ('How to Use MySQL Update Statement', '99-08-01'),
    ('How to Use MySQL Delete Statement', '99-08-05');

Now, let’s fetch all the rows and see what MySQL did to the year values having double digits.

-- Print all rows with date values
SELECT 
    *
FROM
    Tutorials;

The result set is as follows:

1	How to Use MySQL Insert Statement	2069-07-21	9999-12-31
2	How to Use MySQL Select Statement	2069-07-14	9999-12-31
3	How to Use MySQL Update Statement	1999-08-01	9999-12-31
4	How to Use MySQL Delete Statement	1999-08-05	9999-12-31

So, you can check that MySQL converted the years 69 to 2069 and 99 to 1999.

Also Read: MySQL FROM_UNIXTIME() Function

MySQL DATE functions

MySQL supports a bunch of date utility functions that we can use to handle DATE efficiently.

NOW()

This date function returns the current date and time of the running server instance.

-- Print current date and time in MySQL
SELECT NOW();

Its output is:

2019-08-04 09:07:10

DATE()

This date function extracts and returns the date part from the given DATETIME value.

-- Print date part from current date and time in MySQL
SELECT DATE(NOW());

Its output is as follows:

2019-08-04

CURDATE()

It is a simple date function that fetches the current date of the system running the MySQL instance.

-- Print the current date in MySQL
SELECT CURDATE();

The result is as follows:

2019-08-04

DATE_FORMAT()

Sometimes you need to display a date in a user-defined style. For example, you want to show the month first, then the date, and the year in the last.

-- Print the current MySQL date in a user-defined format
SELECT DATE_FORMAT(DATE(NOW()), '%m_%d_%Y') Styled_date;

Its output is going to be:

08_04_2019

DATEDIFF()

You may want to count the difference between the two dates. Therefore, you can use the DATEDIFF() function.

-- Print the difference between two dates
SELECT DATEDIFF('2019-08-04','2019-08-01') diff;

The DATEDIFF() function would subtract the second date argument from the first and return the diff in days.

3

DATE_ADD()

It enables you to add any of the days, weeks, months, or years to a given date. Check the below example.

-- Adding days, weeks, months, and years using DATE_ADD()
SELECT 
    '2019-08-04' ACTUAL,
    DATE_ADD('2019-08-04', INTERVAL 1 DAY) 'Added 1 day',
    DATE_ADD('2019-08-04', INTERVAL 1 WEEK) 'Added 1 week',
    DATE_ADD('2019-08-04', INTERVAL 1 MONTH) 'Added 1 month',
    DATE_ADD('2019-08-04', INTERVAL 1 YEAR) 'Added 1 year';

The result of the date addition operation is as follows:

2019-08-04	2019-08-05	2019-08-11	2019-09-04	2020-08-04

For details, check here: MySQL DATE_ADD()

DATE_SUB()

It enables you to subtract any of the days, weeks, months, or years from a given date. Check the below example.

-- Subtracting days, weeks, months, and years using DATE_SUB()
SELECT 
    '2019-08-04' ACTUAL,
    DATE_SUB('2019-08-04', INTERVAL 1 DAY) 'Subtracted 1 day',
    DATE_SUB('2019-08-04', INTERVAL 1 WEEK) 'Subtracted 1 week',
    DATE_SUB('2019-08-04', INTERVAL 1 MONTH) 'Subtracted 1 month',
    DATE_SUB('2019-08-04', INTERVAL 1 YEAR) 'Subtracted 1 year';

The result of the date subtraction operation is as follows:

2019-08-04	2019-08-03	2019-07-28	2019-07-04	2018-08-04

For details, check here: MySQL DATE_SUB()

Summary

We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL DATE data type and Date functions. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, you must read our step-by-step MySQL tutorial.