MySQL DATE_FORMAT()

In SQL language, DATE_FORMAT() function allows formatting a DATE data in the indicated format. It is the ideal function if you want to define the date formatting directly from the SQL language.
 

Syntax:

DATE_FORMAT() function is used with the following syntax:

SELECT DATE_FORMAT(date, format)

The first parameter is DATE (or DATETIME) data type, while the second parameter is a string containing the formatting choice:

• %a: day of the week (Sun, Mon, … Sat)
• %b: month (Jan, Feb, … Dec)
• %c: month number (0, 1, 2, … 12) (numeric)
• %D: day number, with suffix (0th, 1st, 2nd, 3rd, …)
• %d: day number of the month, with 2 decimals (00..31) (numeric)
• %e: day number of the month (0..31) (numeric)
• %f: microseconds (000000..999999)
• %H: hour (00..23)
• %h: time (01..12)
• %I: hour (01..12)

 


 
• %i: minutes (00..59) (numeric)
• %j: day of the year (001..366)
• %k: hour (0..23)
• %l: hour (1..12)
• %M: month name (January..December)
• %m: month (00..12) (numeric)
• %p: AM or PM
• %r: time in 12-hour format (hh:mm:ss followed by AM or PM)
• %S: seconds (00..59)
• %s: seconds (00..59)
• %T: time in 24-hour format (hh:mm:ss)
• %U: Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
• %u: Week (00..53), where Monday is the first day of the week; WEEK() mode 1
• %V: Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
• %v: Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
• %W: day name of the week (Sunday, Monday, … Saturday)
• %w: day number of the week (0=Sunday, 1=Monday, … 6=Saturday)
• %X: week number of the year, for which Sunday is the first day of the week, numeric, 4 digits; used with %V
• %x : week number of the year, for which Monday is the first day of the week, numeric, 4 digits; used with %V
• %Y: year, numeric (with 4 digits)
• %y: year, numeric (with 2 digits)
• %%: one character %.
• %x x, for any “x” not listed above

 

 

Example :

It is possible to combine several values below in order to get the date formatting in the format of your choice. Here is a list of examples using DATE_FORMAT() function in SQL queries:

SELECT DATE_FORMAT("2017-09-12", "%D %b %Y");
-- output: "12th Sep 2017"
SELECT DATE_FORMAT("2017-09-12", "%M %d %Y");
-- output: "September 12 2017"
SELECT DATE_FORMAT("2017-09-12 02:21:20", "%W %M %e %Y");
-- output: "Monday September 12 2017"
SELECT DATE_FORMAT("2017-09-12", "%d/%m/%Y");
-- output: "12/09/2017"
SELECT DATE_FORMAT("2017-09-12", "Today is : %d/%m/%Y");
-- output: "Today is : 12/09/2017"
SELECT DATE_FORMAT("2017-09-12 02:21:20", "%H:%i:%s");
-- output: "02:21:20"

 

 
The examples above are just a few use cases, the advantage of the DATE_FORMAT() function is that it is possible to combine the formatting in any way you like.

Example of use in a real SQL query:

SELECT *, DATE_FORMAT(registration_date, "%d/%m/%Y")
FROM users;

This query provides a list of users, as well as the date of registration with day/month/year formatting ready to be used displayed in a format that is easy to read for users.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More