Formatting MySQL Dates

MySQL's DATE_FORMAT() function provides a wide range of format codes that you can use to represent date and time values in various formats. 

It allows you to format a date or datetime value as a string using custom formatting.

DATE_FORMAT(date, format)

date: The date or datetime value you want to format.

format: A string specifying the desired format for the output. This string can include special format codes that represent various components of the date and time (e.g., %Y for the year, %m for the month, %d for the day, %H for the hour, %i for the minute, %s for the second, etc.).

SELECT DATE_FORMAT('2023-09-03', '%M %d, %Y'); 
// returns 'September 03, 2023'.

Here is a list of commonly used format codes along with examples:

%Y: Four-digit year (e.g., '2023').
%y: Two-digit year (e.g., '23' for 2023).
%m: Month (01-12).
%c: Month (1-12).
%d: Day of the month (01-31).
%e: Day of the month (1-31).
%H: Hour (00-23).
%h: Hour (01-12).
%I: Hour (01-12).
%i: Minutes (00-59).
%s: Seconds (00-59).
%p: AM or PM.
%W: Weekday name (e.g., 'Monday').
%a: Abbreviated weekday name (e.g., 'Mon').
%M: Month name (e.g., 'September').
%b: Abbreviated month name (e.g., 'Sep').
%U: Week (00-53), where Sunday is the first day of the week.
%u: Week (01-53), where Monday is the first day of the week.
%V: Week (01-53), where Sunday is the first day of the week and the first week containing a Thursday is considered the first week.
%T: Time in 24-hour format ('HH:MM:SS').
%r: Time in 12-hour format ('hh:mm:ss AM/PM').
%D: Day of the month with ordinal suffix (e.g., '1st', '2nd', '3rd').
%j: Day of the year (001-366).
%X: Year for the week where Sunday is the first day of the week (e.g., '2023').
%x: Two-digit year for the week where Sunday is the first day of the week (e.g., '23' for 2023).
%%: A literal '%' character.

Here are some examples of how to use these format codes:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2023-09-03
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); -- 14:30:00
SELECT DATE_FORMAT(NOW(), '%M %e, %Y'); -- September  3, 2023
SELECT DATE_FORMAT(NOW(), '%r'); -- 02:30:00 PM

conclusion

You can mix and match these format codes to create custom date and time representations that suit your specific needs. Additionally, you can include other characters (such as dashes, colons, and spaces) in the format string to format the output exactly as you want.