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:

1+------------+
2| day        |
3+------------+
4| 2022-04-12 |
5| 2021-08-09 |
6| 2020-06-26 |
7+------------+

The expected query result would be:

1+-------------------------+
2| day                     |
3+-------------------------+
4| Tuesday, April 12, 2022 |
5| Monday, August 9, 2021  |
6| Friday, June 26, 2020   |
7+-------------------------+

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:

1%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:

12022-04-12 -> DATE_FORMAT('2022-04-12', '%W, %M %e, %Y') -> Tuesday, April 12, 2022
22021-08-09 -> DATE_FORMAT('2021-08-09', '%W, %M %e, %Y') -> Monday, August 9, 2021
32020-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:

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