How to Get the Date from a Datetime Column in MySQL

Problem:

You’d like to get the date from a date and time column in a MySQL database.

Example:

Our database has a table named travel with data in the columns id, first_name, last_name, and timestamp_of_booking.

For each traveler, let’s get their first and last name and the booking date only. (Note: The timestamp_of_booking column contains both date and time data.)

Solution:

We’ll use the DATE() function. Here’s the query you would write:

SELECT first_name,
       last_name,
       DATE(timestamp_of_booking) 
         AS  date_of_booking
FROM travel;

Here’s the result of the query:

Discussion:

In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column. (In our example, we use a column of the timestamp data type.)

The DATE() function returns only the date information. In our example, it returns ‘2019-03-31’ for Tom Smith’s booking date. Note that the time information is not included.