How to extract a date with MySQL DATE

The MySQL DATE command lets you extract a date value from a time specification. This function is especially useful for order lists, calls, or other actions.

What is MySQL DATE?

There are numerous parameters 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 management system offers field types for date and time entry. Besides MySQL TIME, MySQL DATETIME, and MySQL TIMESTAMP, MySQL DATE is a particularly 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 information. 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 comparatively simple. MySQL DATE is no exception. The structure basically looks like this:

DATE (Expression)
bash

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

MySQL DATE examples

For a better understanding of the possibilities 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 specification using MySQL DATE. The following example illustrates 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 transactions and accounting

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 extracting the data from the list. The command automatically searches the table and outputs data in chronological order. In our example, we’ll assume that the list is called ‘Orders’.

SELECT DATE (Orderdate) FROM Orders;
bash

The corresponding 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 interesting one, as it outputs days between two dates or timeframes. 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.

Alterative date formatting

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 applications that need to accommodate different time zones, MySQL TIMESTAMP is the right choice. For dates before 1970 or after 2038, MySQL DATETIME is recommended.
  • 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.

Was this article helpful?