Articles Cookbook
Back to list
MySQL

How to Get the Date from a Datetime Column in MySQL

Database:

Operators:

DATE()

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.

idfirst_namelast_nametimestamp_of_booking
1LisaWatson2019-04-20 14:15:34
2TomSmith2019-03-31 20:10:14
3AndyMarkus2019-08-03 10:05:45
4AliceBrown2019-07-01 12:47:54

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:

first_namelast_namedate_of_booking
LisaWatson2019-04-20
TomSmith2019-03-31
AndyMarkus2019-08-03
AliceBrown2019-07-01

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.

Recommended courses:

Recommended articles:

See also:

go to top