Problem

Given a table Days with a day column of date type, the goal is to write an SQL query that will convert each date in the table into a string formatted as "day_name, month_name day, year". The result table should be returned in any order, and the output must be case-sensitive.

Example

Consider the following Days table:

+------------+
| day        |
+------------+
| 2022-04-12 |
| 2021-08-09 |
| 2020-06-26 |
+------------+

The expected query result would be:

+-------------------------+
| day                     |
+-------------------------+
| Tuesday, April 12, 2022 |
| Monday, August 9, 2021  |
| Friday, June 26, 2020   |
+-------------------------+

Approach

To solve this problem, MySQL's DATE_FORMAT function can be used. This function allows you to format a date value based on a given format pattern. The following format pattern can be used to achieve the required output:

%W, %M %e, %Y

This pattern will replace %W with the day name, %M with the month name, %e for the day number without leading zeros, and %Y for the year.

Example

Let's walk through the problem example using this approach:

2022-04-12 -> DATE_FORMAT('2022-04-12', '%W, %M %e, %Y') -> Tuesday, April 12, 2022
2021-08-09 -> DATE_FORMAT('2021-08-09', '%W, %M %e, %Y') -> Monday, August 9, 2021
2020-06-26 -> DATE_FORMAT('2020-06-26', '%W, %M %e, %Y') -> Friday, June 26, 2020

Solution

SQL

To achieve the required result, we can write an SQL query as follows:

SELECT DATE_FORMAT(day, '%W, %M %e, %Y') AS day
FROM Days;