How to use together the date and time format characters in MySQL DATE_FORMAT() function?

We can use both the format characters together in DATE_FORMAT() function. The following example will clarify this −

mysql> SELECT DATE_FORMAT(NOW(), 'The time is %a %h:%i:%s:%f %p'); +-----------------------------------------------------+ | DATE_FORMAT(NOW(), 'The time is %a %h:%i:%s:%f %p') | +-----------------------------------------------------+ | The time is Sun 06:35:06:000000 AM                  | +-----------------------------------------------------+ 1 row in set (0.00 sec)

Above query is using date format character ‘%a’ along with other time format characters.

Following is another example in which both format characters are used together −

mysql> SELECT DATE_FORMAT(NOW(), 'The date & time is %a %D %M %Y %h:%i:%s:%f %p'); +---------------------------------------------------------------------+ | DATE_FORMAT(NOW(), 'The date & time is %a %D %M %Y %h:%i:%s:%f %p') | +---------------------------------------------------------------------+ | The date & time is Sun 29th October 2017 06:37:17:000000 AM         | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)