A Guide To MySQL Date Data Type

In this guide, we will look at MySQL Date data type and other related MySQL date statements like MySQL curdate. But before we do that, we need to first understand why data types like MySQL date and MySQL curdate statements are important. 

Without a proper database management system, handling all the data available in the world today would be a major headache for any company. For the purpose of data handling and storage, MySQL offers one of the most robust and popular database management systems in the industry. 

While our focus today will be on MySQL date and MySQL curdate, let us first take a glimpse at the other data types that MySQL supports. 

Numeric data types – there are a variety of numeric data types that MySQL supports and these are mostly used to store numerals. However, these numeric data types differ in terms of the values they store. The numeric data types that MySQL supports are TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, and DECIMAL.

String data types – like the name suggests these store strings of data with a pre-defined maximum character length. String data types include CHAR, VARCHAR, TINYTEXT, TEXT, BLOB, MEDIUMTEXT, MEDIUMBLOB, LONGTEXT, LONGBLOB, ENUM, and SET.

MySQL date data type – the MySQL date data type goes together with the time data type. This is how MySQL stores things like order purchase dates and order dispatch dates. As with other data types, MySQL data type also boasts several subtypes namely YEAR, DATE, TIME, DATETIME, and TIMESTAMP.

Now that we have a general overview of the data types that MySQL supports, let us focus on MySQL date data type and also see how, when and where we use MySQL curdate.

Introducing The Date Data Type In MySQL

This guide will show you how to handle date data effectively in MySQL using a variety of MySQL date functions and statements. At the end of this post, you should be comfortable inserting MySQL date data and manipulating it.

There are five temporal data types in MySQL that you can use to manage dates, and MySQL date is just one of those temporal data types. While you may want to use your own date format when working with your database, MySQL supports its own date format: yyyy-mm-dd. If you were considering changing the format and using your own, forget it as this is fixed.

However, if you prefer using a format such as mm-dd-yyyy, you can leverage the function DATE_FORMAT which we will talk about later on in this post. MySQL date data type offers support for date values starting from 1000-01-01 to 9999-12-31 and each date value uses 3 bytes of storage.

Should you want to store dates that are out of range, you can only store them as integers, which means you would have to create three columns, one for the year, another for the month, and the last for the day.

Double-Digit Year In A Date

Primarily, the data value that MySQL stores for the year are four digits long. Nonetheless, you can still store double-digit year values although there are rules around double-digit year values. For starters, MySQL automatically converts any year value in the range 00-69 into 2000-2069. Ranges between 70 and 99 are converted to 1970-1999.

To see things in action, let us get into the practical parts. We will create a table into which we will add data:

Create table persons (
id INT AUTO_INCREMENT PRIMARY KEY
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth VARCHAR(50) NOT NULL
);

Once the table is up, start adding rows into the table:

INSERT INTO people(first_name, last_name, birth_date)
VALUES('John','Doe','1990-09-01);

Once you have added data into the table, you can query it to see the results. The statement to show the table with the data is:

SELECT
first_name, last_name, birth_date FROM
people;

Now that you have seen how that works out, let us try inserting more data in the table, but this time around, we will use a two-digit year.

INSERT INTO people(first_name, last_name, birth_date)
VALUES('Jack','Daniel','01-09-01'),
('Lily','Bush','80-09-01');

Looking at the first row above, we have gone with the year as 01, placing it in the 00-69 range. MySQL converts that to 2001 as we will see below. In row number two, we have used 80 which fall in the 70-99 range, and thus MySQL converts the year to 1980.

On querying the table, you get the results as below.

Date Functions In MySQL

Other than MySQL curdate, there are a variety of other MySQL date functions as we will see below. The MySQL curdate function is used to show the current date. The format MySQL curdate returns is either ‘YYYY-MM-DD’ or ‘YYYYMMDD’. 

Other popular synonyms of MySQL curdate are CURRENT_DATE and CURRENT_DATE(). 

The NOW () function serves the current date time while MySQL curdate serves up the current system date. The respective syntax for both NOW () and curdate are:

SELECT NOW();

And

SELECT CURDATE();

Other than the MySQL date function we have covered so far, you can customize how MySQL date serves up results using the MySQL DATE_FORMAT function.

Formatting dates in MySQL

If you want to format MySQL dates to a particular format, the DATE_FORMAT function is MySQL’s standard date format change statement. The syntax for this statement is fairly simple as you can see below:

DATE_FORMAT (date,format);

In brackets are the two arguments that MySQL’s DATE_FORMAT function accepts. 

These functions are:

• Date: the date value you want to format – it must be a valid date for it to work.
• Format: this is a format string made up of specifiers that are pre-defined. A percentage character (%) precedes each specifier. 

For the most part in this section, we will look at the specifiers you use with the DATE_FORMAT function and what they mean. We will also list some of the most common DATE_FORMAT strings.

When the DATE_FORMAT function returns a result, the string that is returned features a character set and collation that is dependent on the client’s connections’ settings.

Below we start with the specifiers and what they represent

SpecifierWhat it means%aIt represents weekday names. However, here, the names are abbreviated to three characters e.g. Mon, Tue, Wed, Thu, Fri, Sat, Sun.%bThis represents month names, also abbreviated to three characters – Jan, Feb, Mar%cIf you want to represent the month in numeric form (1, 2, 3 …12), you use this specifier.%DTo format the day of the month with an English suffix, you use the specifier %D – 1st, 2nd, 3rd, 4th etc.%dThe %d specifier enables you to represent the day of the month with a leading zero if it is a single number – 01, 02, 03, 04, 10, 20, 31%eThis one allows you to format the day of the month to only the day’s numeral without adding a leading zero – 1, 2, 3, 4, …31%fIt allows you to store microseconds in a range starting from 000000 to 999999%HTo store 24-hour format hours and include a leading zero such as 00, 01, 02 …23%hThis allows the input of hours in 12-hour format. It also allows the use of a leading zero – 01, 02, 03. Another specifier you can use to get the same result is %I%iThis add minutes and allow the addition of a leading zero – 00, 01 … 59%jYou can also list the day of the year in MySQL. With the specifier %j, you have the ability to add leading zeros to the day – 001, 002, 012, 045, …366%kAdd hours in 24-hour format but remove the leading zero – 0, 1, 2, …23%lThis has the same function as the specifiers %h and %i that allow 12-hour format. However, with this specifier, you do not input a leading zero – 1, 2, … 12%MAllows the use of the month’s full name – January, December%mAdding month names with a leading zero – 00, 01,%pDepending on other time specifiers, include AM or PM%r12-hour format time in the format hh:mm:ss AM or PM%SZero-led seconds – 00, 01 …59. This specifier and %s work the same way%T24-hour format time in the format hh:mm:ss%UYou can even store week numbers. With the %U specifier, the week number is led by a zero if the first day of the week is Sunday.%uThe week number with this specifier only starts with a zero if the first day of the week is on a Monday.%VIt serves the same function as %U and is used simultaneously with %X.%vLike %V, this one functions similarly as %u and used together with %x.%WAllows the entry of full weekday name – Monday, Tuesday, Friday … Saturday.%wStores weekdays in numerals – Sunday is 0, Monday is 1 …Saturday is 6.%XThis is often used with %V. It stores the year for the week (where first day of the week is Sunday) in four-digit format.%xSame as %X but with this specifier, the first day of the week has to be on a Monday. It is used together with %v.%YStores year in the standard four digit format – 1999, 2019%yThis specifier is used for storing dual-digit years – 99,07,19%%To add the % character to the output, use two %% characters as your specifiers

Now that we are privy to the date_format specifiers and what they mean, let us move on to common date format strings.

Sample string for DATE_FORMATActual date after formatting%Y-%m-%d7/13/2019%e/%c/%Y13/7/2019%c/%e/%Y7/13/2019%d/%m/%Y13/7/2019%m/%d/%Y7/13/2019%e/%c/%Y %H:%i13/7/2019 10:30%c/%e/%Y %H:%i7/13/2019 10:30%a %D %b %YSat 13th Jul 2019%a %D %b %Y %H:%iSat 13th Jul 2019 10:30%a %D %b %Y %TSat 13th Jul 2019 10:30:05%a %b %e %YSat Jul 13th 2019%a %b %e %Y %H:%iSat Jul 13th 2019 10:30%a %b %e %Y %TSat Jul 13th 2019 10:30:05%W %D %M %YSaturday 13th July 2019%W %D %M %Y %H:%iSaturday 13th July 2019 10:30%W %D %M %Y %TSaturday 13th July 2019 10:30:05%l:%i %p %b %e, %Y7/13/2019 10:30%M %e %Y13-Jul-2019%a, %d %b %Y %TSat, 13 Jul 2019 10:30:05

As you can see from above, the date function in MySQL is versatile and has numerous formats you can leverage in your database. Whether you want to store order dates or shipping dates, MySQL database management software gives you the power to define how dates will be stored in your database.