Languages
[Edit]
EN

MySQL - DATE_FORMAT() function example

0 points
Created by:
Lia-Perez
505

In this article, we would like to show you DATE_FORMAT() function example in MySQL.

Quick solution

SELECT DATE_FORMAT('2021-01-15', "%e %M %Y");

Where:

  • %e - day of the month as a numeric value (0 to 31),
  • %M - full month name,
  • %Y - year as a numeric, 4-digit value.

Note:

Go to the official documentation to find more date format specifiers such as %e, %M and %Y.

Practical examples

Example 1 - Convert one date

In this example, we will convert example date to the day month year format.

Query:

SELECT DATE_FORMAT('2021-02-01', "%e %M %Y") AS 'formatted_date';

Result:

MySQL - convert date to string - result
MySQL - convert date to string - result

You can also specify the separator such as "-" or "/" between day, month and year.

Query:

SELECT DATE_FORMAT('2021-02-01', "%e-%M-%Y") AS 'formatted_date';

Result:

MySQL - convert date to string - result
MySQL - convert date to string - result

Example 2 - Convert dates in column

To show how to convert date to a string, we will use the following table:

MySQL - example data used to convert date to string
MySQL - example data used to convert date to string

Note:

At the end of this article you can find database preparation SQL queries.

In this example, we will display the registration_time column as string in the day month year format.

Query:

SELECT 
    `id`, `username`,
    DATE_FORMAT(`registration_time`, "%e %M %Y") AS 'registration_date'
FROM `users`;

Result:

MySQL - format date column to string - result
MySQL - format date column to string - result

Example 3 - Convert dates and time

In this example, we will display the registration_time column as string in the day month year hour:minute AM/PM format.

Query:

SELECT 
    `id`, `username`,
    DATE_FORMAT(`registration_time`, "%e %M %Y %h:%i %p") AS 'registration_date'
FROM `users`;

Result:

MySQL - format datetime column to string - result
MySQL - format datetime column to string - result

Where:

  • %h - hour (01 - 12),
  • : - separator between hours and minutes,
  • %i - minutes (00 - 59),
  • %p - AM or PM.

Note:

Go to the official documentation to find more date format specifiers such as %h, %i and %p.

Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL,
	`registration_time` DATETIME NOT NULL,
	PRIMARY KEY (`id`)
);

insert_data.sql file:

INSERT INTO `users`
	(`username`, `registration_time`)
VALUES
	('Tom', '2021-01-01 11:41:31'),
	('Chris','2021-01-02 11:42:45'),
	('Jack','2021-01-03 15:13:39'),
    ('Kim','2021-01-03 15:24:51'),
    ('Marco','2021-01-04 22:35:38'),
	('Kate','2021-01-04 22:46:51'),
	('Nam','2021-01-04 22:57:37');
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

MySQL

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join