MySQL DATE() Function

The DATE() function in MySQL is used to extract the date part from a datetime expression, returning only the date in the format YYYY-MM-DD.

Definition and Usage

• The DATE() function extracts the date part of a datetime expression and returns it as a date.

Syntax

DATE(expression)

Parameter Values

• expression: Required. A valid date or datetime value. If the expression is not a valid date or datetime, it will return NULL.

Technical Details

• Works in: From MySQL 4.0
• Return Value: A string representing the date in YYYY-MM-DD format, or NULL if the expression is not a valid date or datetime.

Examples

Example 1: Extract the date part from a datetime value

SELECT DATE("2017-06-15 09:34:21");

Output:

2017-06-15

This query extracts the date part (2017-06-15) from the given datetime value.

Example 2: Extract the date part from an invalid string

SELECT DATE("The date is 2017-06-15");

Output:

NULL

This query returns NULL because the expression is not a valid datetime value.

Example 3: Extract the date part from a column in a table

SELECT DATE(OrderDate) FROM Orders;

Output:

2017-06-15
2018-03-10
2020-07-21
...

This query extracts the date part from the OrderDate column in the Orders table.

Related Functions

• CURDATE(): Returns the current date.
• NOW(): Returns the current date and time.
• CURRENT_DATE(): Equivalent to CURDATE(), returns the current date.
• DATE_FORMAT(): Formats a date/datetime expression according to the given format.
• DATE_ADD(): Adds a specified time interval to a date.

Use Cases

• Extracting Dates from Datetimes: Useful when you only need the date part from a datetime value and not the time part.
• Filtering and Grouping: Helpful for filtering or grouping data by date while ignoring the time component.
• Data Transformation: When transforming or cleaning data, you might need to isolate the date from a datetime field for further analysis.