1853. Convert Date Format


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;
Discover Your Strengths and Weaknesses: Take Our 2-Minute Quiz to Tailor Your Study Plan:

How does merge sort divide the problem into subproblems?

Solution Implementation


Fast Track Your Learning with Our Quick Skills Quiz:

Which of the following is the prefix sum of array [1, 2, 3, 4, 5]?


Recommended Readings


Got a question? Ask the Monster Assistant anything you don't understand.

Still not clear? Ask in the Forum,  Discord or Submit the part you don't understand to our editors.

Tired of the LeetCode Grind?

Our structured approach teaches you the patterns behind problems, so you can confidently solve any challenge. Get started now to land your dream tech job.

Get Started

🪄