MySQL DATE() Function

 The DATE() function in MySQL is used to extract the date part from a DATETIME or TIMESTAMP value. It returns the date in YYYY-MM-DD format (the default format for dates in MySQL).

Syntax:

DATE(expression)

• expression: This is the value from which the date part is extracted. It can be a DATETIME, TIMESTAMP, or any valid date-related string or column.

Example Usage:

1. Extracting the Date from a DATETIME Value:

Suppose you have a DATETIME value 2024-12-23 14:30:00 and you want to get only the date part (2024-12-23):

SELECT DATE('2024-12-23 14:30:00');

Result:

+---------------------------+
| DATE('2024-12-23 14:30:00') |
+---------------------------+
| 2024-12-23                |
+---------------------------+

2. Using DATE() with a TIMESTAMP Column:

If you have a table with a TIMESTAMP column and you want to extract the date part from it, you can use the DATE() function in a SELECT query.

For example, consider the following table:

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255),
    event_datetime DATETIME
);

Insert some data:

INSERT INTO events (event_name, event_datetime)
VALUES 
('Conference', '2024-12-25 09:00:00'),
('Webinar', '2024-12-26 15:00:00');

Now, extract only the date part of the event_datetime column:

SELECT event_name, DATE(event_datetime) AS event_date
FROM events;

Result:

+------------+------------+
| event_name | event_date |
+------------+------------+
| Conference | 2024-12-25 |
| Webinar    | 2024-12-26 |
+------------+------------+

Important Notes:

• DATE() only works with date or date-time related expressions. It ignores the time part.
• The DATE() function does not modify the original column; it just returns the extracted date part as a result.
• It returns the date in the standard YYYY-MM-DD format, so make sure to use it accordingly for any comparisons or formatting.

Additional Use Cases:

3. Using DATE() with NOW():

You can use the NOW() function to get the current date and time, and then apply DATE() to get just the current date.

SELECT DATE(NOW());

Result:

+---------------------+
| DATE(NOW())         |
+---------------------+
| 2024-12-23          |
+---------------------+

4. Using DATE() in WHERE Clause:

If you want to filter records based on a date, you can use DATE() in the WHERE clause.

SELECT * FROM events
WHERE DATE(event_datetime) = '2024-12-25';

Result: This will return all events that occur on 2024-12-25.

Summary:

• The DATE() function is useful for extracting just the date part of a DATETIME, TIMESTAMP, or similar expressions.
• It can be used in SELECT, WHERE, or any query where you need to deal with just the date.
• It returns the date in YYYY-MM-DD format.