MySQL DATE_FORMAT() function

May 15, 2021 MySQL

Definitions and usages

DATE_FORMAT() function is used to display date/time data in different formats.

DATE_FORMAT(date,format)

The date parameter is a legitimate date. Format specifies the output format for the date/time.

The formats that can be used are:

Format	describe
%a	Abbreviated week name
%b	Abbreviation
%c	Month, value
%D	Day in the month of English prefix
%d	Month day, value (00-31)
%e	Month day, value (0-31)
%f	Microseconds
%H	Hours (00-23)
%h	Hours (01-12)
%I	Hours (01-12)
%i	Minutes, values (00-59)
%j	Year of the year (001-366)
%k	Hours (0-23)
%l	Hours (1-12)
%M	Month
%m	Month, numerical (00-12)
%p	AM or PM
%r	Time, 12-hour (HH: mm: SS AM or PM)
%S	Second (00-59)
%s	Second (00-59)
%T	Time, 24-hour (hh: mm: ss)
%U	Zhou (00-53) Sunday is the first day of the week
%u	Week (00-53) Monday is the first day of the week
%V	Zhou (01-53) Sunday is the first day of the week, uses% x
%v	Week (01-53) Monday is the first day of the week, uses% x
%W	Week name
%w	Zhou's Day (0 = Sunday, 6 = Saturday)
%X	Year, the Sunday is the first day, 4 digits, use with% v
%x	Year, of which Monday is the first day, 4 digits, use with% v
%Y	Year, 4
%y	Year, 2

The following script uses DATE_FORMAT() function to display different formats. We use NOW() to get the current date/time:

select  
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'),
DATE_FORMAT(NOW(),'%m-%d-%Y'),
DATE_FORMAT(NOW(),'%d %b %y'),
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

The result is as follows:

Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243