MySQL DATE_FORMAT() Function with Examples


How to Use the MySQL DATE_FORMAT() Function

In this tutorial, you'll learn how to use the MySQL DATE_FORMAT() function.
DATE_FORMAT() formats a date as specified.

MySQL DATE_FORMAT() Syntax

The syntax of the DATE_FORMAT() function is as follows:

DATE_FORMAT(date, format)

Arguments

  • date: Required. The date that you want to format.
  • format: Required. The format to use.

Below is a list of the format string you can use to format a date:

FormatDescription
%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; WEEK() mode 0
%uWeek (00..53), where Monday is the first day of the week; WEEK() mode 1
%VWeek (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%vWeek (01..53), where Monday is the first day of the week; WEEK() mode 3; 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

Note that you also can use the combination of format values.

DATE_FORMAT() Examples

Example 1

The following statement formats a date:

SELECT DATE_FORMAT('2019-10-04 20:45:20', '%W %M %Y') AS FormattedDate;

Returned value:

FormattedDate
Friday October 2019

Example 2

The following statement formats a date:

SELECT DATE_FORMAT('2019-10-08 19:34:30', '%H:%i:%s') AS FormattedHour;

Returned value:

FormattedHour
19:34:30

Example 3

The following statement formats a date:

SELECT DATE_FORMAT('2019-10-09 23:30:00', '%W %D %M %Y %h:%i %p') AS FormattedDate;

Returned value:

FormattedDate
Wednesday 9th October 2019 11:30 PM

Example 4

The following statement formats a date:

SELECT DATE_FORMAT('2019-10-08 19:34:30', '%h:%i:%s %p') AS FormattedHour;

Returned value:

FormattedHour
07:34:30 PM

Example 5

The following statement formats a date:

SELECT DATE_FORMAT('2019-10-08 10:34:30', '%h:%i:%s %p') AS FormattedHour;

Returned value:

FormattedHour
10:34:30 AM

In this tutorial, you've learned how to use the MySQL DATE_FORMAT() function. DATE_FORMAT() formats a date as specified.

Leave a Reply

Your email address will not be published. Required fields are marked *