MySQL DATE_FORMAT()

In this tutorial, we will learn about the MySQL DATE_FORMAT() function. The date value in MySQL is in the format “YYYY-MM-DD”. However, such a date value may be difficult to read for some people – who prefer it in some other format like DD/MM/YYYY or MM/DD/YYYY. Some people may prefer to see the day name value as well. While sometimes, you may want to display the month name instead of month number in the date format. This is where the MySQL DATE_FORMAT() function comes into play.

The MySQL DATE_FORMAT() function formats a date value with a given specified format. You may also use MySQL DATE_FORMAT() on datetime values and use some of the formats specified for the TIME_FORMAT() function to format the time value as well. Let us take a look at the syntax of DATE_FORMAT() and some examples.

Syntax of MySQL DATE_FORMAT()

DATE_FORMAT(date, format)Code language: SQL (Structured Query Language) (sql)

Where ‘date’ is the date/datetime value that needs to be formatted and,

‘format’ is the format to be used. This value should be in quotes.

The ‘format’ can be one or a combination of the following –

%a – Abbreviated weekday name (Sun to Sat)
%b – Abbreviated month name (Jan to Dec)
%c – Numeric month name (0 to 12)
%D – Day of the month as a numeric value, followed by a suffix (1st, 2nd, 3rd, …)
%d – Day of the month as a numeric value (01 to 31)
%e – Day of the month as a numeric value (0 to 31)
%f – Microseconds (000000 to 999999)
%H – Hour (00 to 23)
%h – Hour (00 to 12)
%I – Hour (00 to 12)
%i – Minutes (00 to 59)
%j – Day of the year (001 to 366)
%k – Hour (0 to 23)
%l – Hour (1 to 12)
%M – Month name in full (January to December)
%m – Month name as a numeric value (00 to 12)
%p – AM or PM
%r – Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S – Seconds (00 to 59)
%s – Seconds (00 to 59)
%T – Time in 24 hour format (hh:mm:ss)
%U – Week where Sunday is the first day of the week (00 to 53)
%u – Week where Monday is the first day of the week (00 to 53)
%V – Week where Sunday is the first day of the week (01 to 53). Used with %X
%v – Week where Monday is the first day of the week (01 to 53). Used with %x
%W – Weekday name in full (Sunday to Saturday)
%w – Day of the week where Sunday=0 and Saturday=6
%X – Year for the week where Sunday is the first day of the week. Used with %V
%x – Year for the week where Monday is the first day of the week. Used with %v
%Y – Year as a numeric, 4-digit value
%y – Year as a numeric, 2-digit value

Examples of MySQL DATE_FORMAT()

Let us start with a basic example. Let us format the date value – ‘2021-01-15’. The first query should only display the year value from the date. This would be the equivalent of the YEAR() function. The second query should display only the month value from the date. This would be the equivalent of the MONTH() function. Let us write these queries using the SELECT statement and aliases. The query is –

SELECT DATE_FORMAT('2021-01-15', '%Y') AS Result; 
SELECT DATE_FORMAT('2021-01-15', '%m') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Using Combination of Formats

For the date value – ‘2021-01-15′, let us display the date in the format “DD/MM/YY”. For this, we will need a combination of the format parameters. For the day values, we can use %d. To display the month value, we will use %m and for the two-digit year value, we will use %y. We will specify a / between %d, %m, and %y. The query is –

SELECT DATE_FORMAT('2021-01-15', '%d/%m/%y') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL DATE_FORMAT() With NOW()

Let us use datetime values with MySQL DATE_FORMAT(). Since NOW() returns the current datetime value, we will use it in the query below. Let us format the result of the NOW() function to “DD/MM/YY HH:MM:SS AM/PM”. For the time formatting, we will use %r. The query is –

SELECT DATE_FORMAT(NOW(), '%d/%m/%y %r') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Displaying Day and Month Names

Let us write a query that displays the date in the format – ‘day_name, day_of_month month_name YYYY’. For displaying the day name, we will use %W. For displaying the day of the month, we will use %D. To display the full month name, we will use %M. The query is –

SELECT DATE_FORMAT('2021-01-15', '%W, %D  %M %Y') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Lastly, let us display the date in a similar format as above, but this time, we will display short names for the day name and month name. We will also display the year as a two-digit value and the day of the month without the suffix. The query is –

SELECT DATE_FORMAT('2021-01-15', '%a, %d %b %y') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below BusStop table.

Let us write a query that displays the bus details and the departure date formatted with the DATE_FORMAT() function with the short names of day name and month name. The year value should be a four-digit value and the day of the name should be followed by a suffix. We should also display the departure time formatted as “HH:MM:SS AM/PM”. We will use the TIME_FORMAT() function for the latter part. The query is –

SELECT Bus_No, Name, Start, End, 
DATE_FORMAT(DepartureTime, '%a, %D %b %Y') AS DepartureDate, 
TIME_FORMAT(DepartureTime, '%r') AS DepartureTime 
FROM BusStop;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

Formatting date and time values is a very important operation in MySQL and is widely used in a lot of use cases – especially when we have to make the date and time value presentable for the user. I would highly encourage you to practice a couple of examples of this function.

References

MySQL Official documentation on the DATE_FORMAT() function.