MySQL DATE_FORMAT() Function

 The DATE_FORMAT() function in MySQL is used to format a date value according to a specified format string. This function is particularly useful when you want to display date or time information in a custom format.

Syntax:

DATE_FORMAT(date, format)

• date: The date or datetime value to format.
• format: The format string that defines how the date or datetime should be displayed.

Common Format Specifiers:

Specifier	Description	Example Output
%Y	Year (4 digits)	2024
%y	Year (2 digits)	24
%m	Month (2 digits)	12
%c	Month (numeric, no leading zero)	12
%M	Full month name	December
%b	Abbreviated month name	Dec
%d	Day of the month (2 digits)	23
%e	Day of the month (no leading zero)	3
%H	Hour (24-hour clock, 2 digits)	15
%h or %I	Hour (12-hour clock, 2 digits)	03
%p	AM or PM	PM
%i	Minutes (2 digits)	45
%s	Seconds (2 digits)	30
%f	Microseconds (6 digits)	123456
%T	Time (24-hour clock, HH:MM:SS)	15:45:30
%r	Time (12-hour clock, HH:MM:SS AM/PM)	03:45:30 PM
%W	Full weekday name	Monday
%a	Abbreviated weekday name	Mon
%D	Day of the month with suffix	23rd

Examples:

1. Formatting a Date:

   SELECT DATE_FORMAT('2024-12-23', '%W, %M %d, %Y') AS formatted_date;
   

   Output: Monday, December 23, 2024

2. Formatting a Time:

   SELECT DATE_FORMAT('2024-12-23 15:45:30', '%h:%i %p') AS formatted_time;
   

   Output: 03:45 PM

3. Combining Date and Time:

   SELECT DATE_FORMAT('2024-12-23 15:45:30', '%M %d, %Y at %h:%i %p') AS custom_datetime;
   

   Output: December 23, 2024 at 03:45 PM

4. Using Current Date:

   SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_datetime;
   

   Output: 2024-12-23 15:45:30

This function is highly versatile for creating user-friendly date and time displays in MySQL queries.