How to format date fields using MySQL

No Comments

Ever wondered how to change the output of the date format using MySQL?

If you are constantly logging you’re data it’s more likely that you will use DATETIME fields in a MySQL table field to track the dates of events.

What happens if you need to display the date in Human readable format? By using the DATE_FORMAT() function you’ll be able to manipulate the Date in almost any possible way.

MySQL Example using the DATE_FORMAT() function:

DATE_FORMAT(NOW(),‘%W, %M %e, %Y @ %h:%i %p’)  will output ‘Sunday, September 20, 2008 @ 12:45 PM’ 

Letter Representations for the mySql DATE_FORMAT() function

SpecifierDescription
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal “%” character
%xx, for any “x” not listed above

Image Credits: Photo by Isis França on Unsplash.

Last Updated On March 23, 2018

Related Articles

No results found

Leave a Reply

avatar
  Subscribe  
Notify of
Menu
Copy link