MySQL Date Time Cheat Sheet

 Format Character  Description  Returned Values
HOUR
%H 24-hr format with leading zero 00 - 23
%h 12-hr format with leading zero 01 - 12
%P Uppercase Ante meridiem and Post meridiem AM/PM
%p Lowercase Ante meridiem and Post meridiem am/pm
%r Time, 12-hour (hh:mm:ss followed by AM or PM)  
Minutes and Seconds
%i Minutes with leading zeros 00 - 59
%S Seconds with leading zeros 00 - 59
%f Microseconds 000000 - 999999
 Format Character  Description  Returned Values
Day of Week
%a A textual representation of a day, three letters Mon - Sun
%U Week (00..53), where Sunday is the first day of the week 00 - 53
%u Week (00..53), where Monday is the first day of the week 00 - 53
%V Week (01..53), where Sunday is the first day of the week; used with %X 01 - 53
%v Week (01..53), where Monday is the first day of the week; used with %x 01 - 53
%W Weekday name Sunday - Saturday
%w Day of the week (0=Sunday..6=Saturday) 0 is Sunday - 6 is Saturday
Day of Month
%d Day of the month with leading zeros 01 - 31
%e Day of the month without leading zeros 1 - 31
%D English ordinal suffix for the day of the month st, nd, rd or th
(1st, 2nd, 3rd, 4th...)
Month
%M A full textual representation of a month, such as January or March January - December
%b A short textual representation of a month, three letters Jan - Dec
%ac Numeric representation of a month, with leading zero 01 - 12
Year
%Y A full numeric representation of a year, 4 digits 1999, 2000, 2001 etc
%y A two digit representation of a year 99, 00, 01 etc
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V  
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v  
%j The day of the year (starting from 0) 001 - 366