DATE_FORMAT Specifiers
Day
%a Day of the week in 3-letter abbreviation (Sun, Mon, etc.)
%D Day of the month abbreviated (1st, 2nd, etc.)
%d Day of the month in 2-digit numeric indicator (00-31)
%e Day of the month in numeric indicator (0-31)
%j Day of the year in 3-digit display (001-366)
%W Day of the week in full word (Sunday, Monday, etc.)
%w Day of the week in numeric form (1=Sunday, 2=Monday, etc.)
Month
%b Month of the year in 3-letter abbreviation (Jan, Feb, etc.)
%c Month of the year in numeric indicator (0-12)
%M Month of the year in full word (January, February, etc.)
%m Month of the year in 2-digit numeric indicator (00-12)
Year
%X Year in 4-digit numeric display, beginning with Sunday, for use with %V
%x Year in 4-digit numeric display, beginning with Sunday, for use with %v
%Y Year in 4-digit numeric display
%y Year in 2-digit numeric display
Week
%U Week of the year (00-53), beginning with Sunday
%u Week of the year (00-53), beginning with Monday
%V Week of the year (01-53), beginning with Sunday, for use with %X
%v Week of the year (01-53), beginning with Sunday, for use with %x
Hour
%H Hour in 2-digit, 24-hour time (00-23)
%h, %I Hour 2-digit, in 12-hour time (01-12)
%k Hour in 24-hour time (0-23)
%l Hour in 12-hour time (1-12)
Minute
%I Minute in 2-digit display (00-59)
%p Before noon or after noon (AM or PM)
Second
%f Microseconds in 6-digit display (000000-999999)
%S, %s Seconds in 2-digit numeric indicator (00-59)
Time
%r Time in 12-hour display, formatted HH:MM:SS, with AM or PM
%T Time in 24-hour display, formatted HH:MM:SS
Percent
%% Display of the percent symbol (%)
Related examples in the same category
| 1. | Format a date 1 | | |
| 2. | Format a date 2 | | |
| 3. | Format a date 3 | | |
| 4. | Format a date 4 | | |
| 5. | Format a date 5 | | |
| 6. | Date Symbols in DATE_FORMAT, TIME_FORMAT, and FROM_UNIXTIME | | |
| 7. | Time Symbols in DATE_FORMAT, TIME_FORMAT, and FROM_UNIXTIME | | |
| 8. | Common used flags for DATE_FORMAT() | | |
| 9. | DATE_FORMAT function | | |
| 10. | SELECT DATE_FORMAT('2005-12-31', '%M %d %Y') | | |
| 11. | SELECT DATE_FORMAT('2005-12-31', '%D of %M') | | |
| 12. | To return the birthdays of all staff in the format MM/DD/YYYY, use the DATE_FORMAT() function | | |
| 13. | SELECT DATE_FORMAT("2010-08-30 21:19:58", "%W %M %d %Y"); | | |
| 14. | SELECT DATE_FORMAT("2010-08-30 21:19:58", "%a. %b %e, '%y"); | | |
| 15. | SELECT DATE_FORMAT("2010-08-30 21:19:58", "%m-%e-%Y %l:%i%p"); | | |
| 16. | SELECT DATE_FORMAT("2010-08-30 21:19:58", "%m-%e-%Y %h:%i%s%p %W"); | | |
| 17. | SELECT DATE_FORMAT("2010-08-30 21:19:58", "%M %D,%Y %k:%i CST %W"); | | |
| 18. | Formatting Dates and Times | | |
| 19. | SELECT @@GLOBAL.DATETIME_FORMAT | | |
| 20. | SET @@SESSION.DATETIME_FORMAT = DEFAULT | | |
| 21. | Decomposing Dates and Times Using Formatting Functions | | |
| 22. | Telling MySQL How to Display Dates or Times | | |
| 23. | The DATE and TIME data types use the following format: | | |
| 24. | Present a date differently than in CCYY-MM-DD format or present a time without the seconds part | | |
| 25. | Calculating One Date from Another by Substring Replacement | | |
| 26. | The string replacement technique can be used to produce dates with a specific position within the calendar year | | |
| 27. | For Christmas, replace the month and day with 12 and 25: | | |
| 28. | %a is the abbreviated weekday name, %D is the day of month with the suffix attached, %b is the abbreviated mon | | |