The Date_format () function is used to display date/time data in different formats.
Grammar
Date_format (Date,format)
The date parameter is a valid day. format Specifies the date/time in which the output is formatted.
The formats you can use are:
format |
Description |
%a |
Abbreviated Week name |
%b |
Abbreviated month name |
%c |
Month, value |
%d |
Day of the month with English prefixes |
%d |
Day of the month, value (00-31) |
%e |
Day of the month, value (0-31) |
%f |
Microseconds |
%H |
Hours (00-23) |
%h |
Hours (01-12) |
%I |
Hours (01-12) |
%i |
Minutes, value (00-59) |
%j |
Days of the Year (001-366) |
%k |
Hours (0-23) |
%l |
Hours (1-12) |
%M |
Month Name |
%m |
month, value (00-12) |
%p |
AM or PM |
%r |
Time, 12-hour (Hh:mm:ss AM or PM) |
%s |
Seconds (00-59) |
%s |
Seconds (00-59) |
%T |
Time, 24-hour (HH:MM:SS) |
%u |
Week (00-53) Sunday is the first day of the week |
%u |
Week (00-53) Monday is the first day of the week |
%V |
Week (01-53) Sunday is the first day of the week, with%x |
%v |
Week (01-53) Monday is the first day of the week, with%x |
%W |
Week name |
%w |
Day of the week (0= Sunday, 6 = Saturday) |
%x |
year, of which Sunday was the first day of the week, 4-bit, with%V used |
%x |
year, of which Monday was the first day of the week, 4-bit, with%v used |
%Y |
Year, 4 guests |
%y |
Year, 2 guests |
Instance
The following script uses the Date_format () function to display different formats. We use now () to get the current date/time:
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 results are similar:
Dec 11:45 pm12-29-200829 Dec 0829 Dec 2008 16:25:46.635
MySQL Date_format () function