Back to list MySQL How to Change Datetime Formats in MySQL Database: MySQL 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: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Find the Number of Days Between Two Dates in MySQL How to Add Days to a Date in MySQL How to Change Seconds to a Time Value in MySQL Tags: MySQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.