MySQL Date and Date Functions Explained with Examples – MPS


mysql date function

This tutorial explains about 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 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 to change 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.

MySQL Date and Date Functions with Examples

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

DATE data type in MySQL

We can use DATE for many purposes, and it is one of the most commonly used data types. MySQL provides 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 type fields. Both these columns will use the default DATE format.

One is dateCreated that we would feed in 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 to 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 the two digits 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 two digits year values.

-- 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 year 69 to 2069 and 99 to 1999.

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();

It’s 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());

It’s output is:

2019-08-04

CURDATE()

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

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

The result is:

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 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 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 returns the diff in days.

3

DATE_ADD()

It enables you to add any of the days or weeks or 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

DATE_SUB()

It enables you to subtract any of the days or weeks or 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

Summary

We hope that after wrapping up this tutorial, you should feel comfortable in 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, do read our step by step MySQL tutorial.

Recommended post:


Source link

MySQL Date and Date Functions Explained with Examples – MPS

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

You have successfully subscribed to myprogrammingschool

There was an error while trying to send your request. Please try again.

My Programming School will use the information you provide on this form to be in touch with you and to provide updates and marketing.