How the DATE() function works in Mariadb?

The DATE() function is a built-in function in Mariadb that extracts the date part from a date or datetime value and returns it as a date value.

Posted on

The DATE() function is a built-in function in Mariadb that extracts the date part from a date or datetime value and returns it as a date value. This function is often used to get the date component of a datetime value without the time component.

Syntax

The syntax of the DATE() function is as follows:

DATE(date)

The date argument is the date or datetime value from which the date part is extracted. The DATE() function returns a date value in the format of YYYY-MM-DD.

Examples

Example 1: Extracting the date from a datetime value

The following example shows how to use the DATE() function to extract the date part from a datetime value.

SELECT DATE('2023-12-17 20:14:30');

The output is:

+-----------------------------+
| DATE('2023-12-17 20:14:30') |
+-----------------------------+
| 2023-12-17                  |
+-----------------------------+

This means that the result is 2023-12-17, which is the date part of the datetime value 2023-12-17 20:14:30.

Example 2: Extracting the date from a date value

The following example shows how to use the DATE() function to extract the date part from a date value.

SELECT DATE('2023-12-17');

The output is:

+--------------------+
| DATE('2023-12-17') |
+--------------------+
| 2023-12-17         |
+--------------------+

This means that the result is 2023-12-17, which is the same as the input date value.

There are some other functions in Mariadb that are related to the DATE() function. Here are some of them:

  • DATE_FORMAT(): This function formats a date or datetime value according to a specified format string and returns the result as a new string value.
  • DATE_ADD(): This function adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value.
  • DATE_SUB(): This function subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value.

For example, the following query shows the usage of the DATE_FORMAT() function, the DATE_ADD() function, and the DATE_SUB() function with the DATE() function.

SELECT DATE_FORMAT(DATE('2023-12-17 20:14:30'), '%M %D, %Y') AS 'Formatted Date',
       DATE_ADD(DATE('2023-12-17 20:14:30'), INTERVAL 10 DAY) AS 'Date Plus 10 Days',
       DATE_SUB(DATE('2023-12-17 20:14:30'), INTERVAL 10 DAY) AS 'Date Minus 10 Days';

The output is:

+---------------------+-------------------+--------------------+
| Formatted Date      | Date Plus 10 Days | Date Minus 10 Days |
+---------------------+-------------------+--------------------+
| December 17th, 2023 | 2023-12-27        | 2023-12-07         |
+---------------------+-------------------+--------------------+

This means that the date part of the datetime value 2023-12-17 20:14:30 is formatted in a custom way, added 10 days, and subtracted 10 days, using different functions.

Conclusion

The DATE() function is a useful function in Mariadb that extracts the date part from a date or datetime value and returns it as a date value. It can be used to get the date component of a datetime value without the time component. There are some other functions in Mariadb that are related to the DATE() function, such as DATE_FORMAT(), DATE_ADD(), and DATE_SUB(). These functions can be used to perform different operations with the date values in different formats and scenarios.