The MySQL DATE command lets you extract a date value from a time spec­i­fi­ca­tion. This function is es­pe­cial­ly useful for order lists, calls, or other actions.

What is MySQL DATE?

There are numerous pa­ra­me­ters that can be used to create a better overview and order in MySQL. These can help you sort tables and grant faster access if needed. For this, the database man­age­ment system offers field types for date and time entry. Besides MySQL TIME, MySQL DATETIME, and MySQL TIMESTAMP, MySQL DATE is a par­tic­u­lar­ly practical and at the same time very simple command to simplify day-to-day work.

MySQL DATE is used for values that contain a date but no time in­for­ma­tion. The function then pulls the date from a statement and maps it. The entries are always stored in the format “YYYY-MM-DD” with values ranging from “1000-01-01” to “9999-12-31”.

MySQL DATE syntax

When you learn MySQL and already know commands like MySQL DELETE, MySQL REPLACE and MySQL CONCAT, you know that the syntax of the system is com­par­a­tive­ly simple. MySQL DATE is no exception. The structure basically looks like this:

DATE (Expression)
bash

It’s important that the ex­pres­sion has an allowed date value. If this isn’t the case, the output will be a null value.

MySQL DATE examples

For a better un­der­stand­ing of the pos­si­bil­i­ties that MySQL DATE offers, some practical examples can be helpful. We will start with the simplest form, where only the date is displayed. The matching command looks like this:

SELECT DATE ("2022-01-10");
bash

When you execute this command, the output is:

2022-01-10
bash

Extract date from a time entry

However, you also have the option to extract the date from a longer time spec­i­fi­ca­tion using MySQL DATE. The following example il­lus­trates this:

SELECT DATE ("2022-01-10 10:17:36");
bash

Again, the output is:

2022-01-10
bash

Store a zero value with MySQL DATE

However, if no valid date value is entered, no output is returned. An example looks like this:

SELECT DATE ("The date is 10.01.2022");
bash

MySQL DATE for order trans­ac­tions and ac­count­ing

The next example provides a handy use case of MySQL DATE. Here we’ll assume that a company has created tables for ordered and shipped products. To track when a product was ordered, you would usually have to go through the lists manually and invest a lot of time. MySQL DATE, on the other hand, speeds up the process by ex­tract­ing the data from the list. The command au­to­mat­i­cal­ly searches the table and outputs data in chrono­log­i­cal order. In our example, we’ll assume that the list is called “Orders”.

SELECT DATE (Orderdate) FROM Orders;
bash

The cor­re­spond­ing output in this case looks like this:

DATE (Orderdate)
2021-11-27
2021-11-29
2022-01-10
2022-02-04
2022-02-17
bash

Related commands

In addition to MySQL DATE, there are several related commands to help you maintain and edit tables. MySQL DATEDIFF is another in­ter­est­ing one, as it outputs days between two dates or time­frames. Other important commands include MySQL DATE_ADD, which adds time values in interval form to a date value, MySQL DATE_FORMAT, which lets you format a date, and MySQL DATE_SUB, which lets you subtract a time value from a date value.

Al­ter­ative date for­mat­ting

Besides MySQL DATE, the system knows three other date formats:

  • MySQL DATETIME: This option stores date and time in the format YYYY-MM-DD HH:MI:SS. The possible time frame ranges from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
  • MySQL TIMESTAMP: This format is similar to MySQL DATETIME, but it takes time zones into account and converts the values to universal time (UTC). The output also follows the YYYY-MM-DD HH:MI:SS order, but the allowed values are between 1970-01-01 00:00:01 UTC and 2038-01-09 03:14:17 UTC. That’s why, for ap­pli­ca­tions that need to ac­com­mo­date different time zones, MySQL TIMESTAMP is the right choice. For dates before 1970 or after 2038, MySQL DATETIME is rec­om­mend­ed.
  • MySQL TIME: MySQL TIME stores the time, but not the date. The format used is HH:MI:SS and the allowed time frame ranges from -838:59:59 to 838:59:59. Besides time points, MySQL TIME can also be used to represent time frames. For example, a day would be 24:00:00.

If MySQL DATE isn’t the right choice for you, you can always fall back on one of these other options.

Go to Main Menu