Leetcode 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;
Got a question?ย Ask the Teaching 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.