How to use the MySQL DATE_FORMAT() function

The DATE_FORMAT() function in MySQL is used to format a date value based on a specified date/time format string. This allows formatting dates in different ways.

Syntax

The syntax for DATE_FORMAT() is:

DATE_FORMAT(date, format)

Where date is the date to format, and format specifies how to format it.

Examples

Format date to YYYY/MM/DD format:
```
SELECT DATE_FORMAT('2023-01-31', '%Y/%m/%d');
```
Returns ‘2023/01/31’.
Format date to MM-DD-YYYY format:
```
SELECT DATE_FORMAT('2023-12-05', '%m-%d-%Y');
```
Returns ‘12-05-2023’.
Format date to day name, month name format:
```
SELECT DATE_FORMAT('2023-02-28', '%W %M');
```
Returns ‘Tuesday February’.
Format datetime to 24-hour time format:
```
SELECT DATE_FORMAT('2023-01-01 11:30:45', '%H:%i:%s');
```
Returns ‘11:30:45’.
Format datetime to 12-hour time with AM/PM:
```
SELECT DATE_FORMAT('2023-01-01 15:45:30', '%r');
```
Returns ‘03:45:30 PM’.

Other Similar Functions

STR_TO_DATE() - Convert string to date
DATE_ADD() - Add interval to date
DATE_SUB() - Subtract interval from date
DATEDIFF() - Calculate date difference

So DATE_FORMAT() provides extensive formatting options for dates and times in MySQL.