Articles Cookbook
Back to list
MySQL

How to Change Datetime Formats in MySQL

Database:

Operators:

DATE_FORMAT()

Problem:

You’d like to change the format of date and time data in a MySQL database.

Example:

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

idfirst_namelast_nameregistration_datetime
1LoraLorens2019-02-23 12:04:23
2AnneSmith2018-07-10 10:12:15
3TomJackson2019-03-09 08:20:33
4RichardWilliams2018-09-30 06:07:34

For each student, let’s get their first name, last name, and registration date and time. However, we want to display the date and time in the following format: abbreviated weekday name, comma, year, month name, day of month, and the time in hours, minutes, and seconds. It should look like this:

Tue, 2019 February 17 11:18:55

Solution:

We’ll use the DATE_FORMAT() function. Here’s the query you’d write:

SELECT first_name,
       last_name,
       DATE_FORMAT(registration_datetime,’%a, %Y %M %e %H:%i:%s’)
         AS format_registration_datetime
FROM student_platform;

Here’s the result of the query:

first_namelast_nameformat_registration_datetime
LoraLorensSat, 2019 February 23 12:04:23
AnneSmithTue, 2018 July 10 10:12:15
TomJacksonSat, 2019 March 9 08:20:33
RichardWilliamsMon, 2019 September 30 06:07:34

Discussion:

In a MySQL database, the DATE_FORMAT() function allows you to display date and time data in a changed format.

This function takes two arguments. The first is the date/datetime to be reformatted; this can be a date/time/datetime/timestamp column or an expression returning a value in one of these data types. (In our example, we use the registration_datetime column of the datetime data type.)

The second argument is a string containing the desired date and time format. MySQL makes a number of specifiers available, like:

  • %a – Abbreviated weekday name.
  • %Y – Year, in 4-digits.
  • %M – Full name of the month.
  • %e – Day of the month (from 1 – 31).
  • %H – Hour (from 00-23).
  • %i – Minutes (from 00-59).
  • %s – Seconds (from 00-59).

You can learn more about date and time specifiers here, in the official MySQL documentation.

For example, Lora Lorens registered on ‘2019-02-23 12:04:23’. Now, her registration date and time has the new format of ‘Sat, 2019 February 23 12:04:23’.

Recommended courses:

Recommended articles:

See also:

go to top