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.