Latest web development tutorials
 

MySQL DATE_FORMAT() Function


< MySQL Date Functions

Definition and Usage

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

Syntax

DATE_FORMAT(date,format)

Where date is a valid date and format specifies the output format for the date/time.

The formats that can be used are:

Format Description
%aAbbreviated weekday name (Sun-Sat)
%bAbbreviated month name (Jan-Dec)
%cMonth, numeric (0-12)
%DDay of month with English suffix (0th, 1st, 2nd, 3rd, �)
%dDay of month, numeric (00-31)
%eDay of 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 week
%uWeek (00-53) where Monday is the first day of week
%VWeek (01-53) where Sunday is the first day of week, used with %X
%vWeek (01-53) where Monday is the first day of 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 week, four digits, used with %V
%xYear for the week where Monday is the first day of week, four digits, used with %v
%YYear, numeric, four digits
%yYear, numeric, two digits

Example

The following script uses the DATE_FORMAT() function to display different formats. We will use the NOW() function 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 result would look something like this:

Nov 04 2014 11:45 PM
11-04-2014
04 Nov 14
04 Nov 2014 11:45:34:243

< MySQL Date Functions