MySQL - DATE_FORMAT() Function

The MySQL DATE_FORMAT() function accepts a date or date-time value and a format string as parameters. It formats the given date according to the specified format and returns the result.

MySQL holds some default formats for all the temporal datatypes with date and time values listed as follows:

Type Format
DATE YYYY-MM-DD
TIME HH:MM:SS
DATETIME YYYY-MM-DD HH:MI:SS
TIMESTAMP YYYY-MM-DD HH:MI:SS
YEAR YYYY

Format String

However, apart from the default values, you can also format the date or time values as your wish. For that, there are certain characters with predefined meaning using which you can create a format string. They are −

Format Description
%a Weekday name (Sun...Sat)
%b Month name (Jan...Dec)
%c Month, numeric (0...12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d Day of the month, numeric (01...31)
%e Day of the month, numeric (0...31)
%f Microseconds (000000...999999)
%H Hour (00...23)
%h Hour (00...12)
%I Hour (00...12)
%i Minutes, numeric (00...59)
%j Day of year (001...366)
%k Hour (0...23)
%l Hour (1...12)
%M Month name (January...December)
%m Month as numeric value (00...12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00...59)
%s Seconds (00...59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00...53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00...53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01...53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v Week (01...53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W Weekday name (Sunday...Saturday)
%w Day of the week (0=Sunday...6=Saturday)
%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
%Y Year as numeric value, 4-digits
%y Year as numeric value, 2-digits

Syntax

Following is the syntax of MySQL DATE_FORMAT() function −

DATE_FORMAT(date,format);

Parameters

This method accepts two parameters. The same is described below −

  • date: The date or datetime expression that we want to format.

  • format: A string specifying the desired format for the output.

Return value

This function returns a formatted string representing the date or datetime value according to the specified format.

Example

In the following example, we are using the MySQL DATE_FORMAT() function to print the weekday, month, and the year of the given date −

SELECT DATE_FORMAT('2023-11-21', '%W %M %Y') As Result;

Output

This will produce the following result −

Result
Tuesday November 2023

Example

Here, we are fetching the weekday (short), month (short) and the day of the month of the given date −

SELECT DATE_FORMAT('2023-11-21', '%a %b %c') As Result;

Output

Following is the output −

Result
Tue Nov 11

Example

The following query prints the weekday, month, year, and time of day in 12 hours format −

SELECT DATE_FORMAT('2023-11-21 22:23:00','%W %M %Y %r') 
As Result;

Output

Following is the output −

Result
Tuesday November 2023 10:23:00 PM

In the following example, we are creating a table named ORDERS using CREATE TABLE statement −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, we are inserting the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL DATE_FORMAT() function to fetch the day, month, year, and week name for each date value in the "DATE" column −

SELECT OID, DATE, DATE_FORMAT(DATE, '%D %M %Y, %W')
As Result FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 8th October 2009, Thursday
100 2009-10-08 00:00:00 8th October 2009, Thursday
101 2009-11-20 00:00:00 20th November 2009, Friday
103 2008-05-20 00:00:00 20th May 2008, Tuesday
Advertisements