Facebook Pixel

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:

+------------+
| 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;

Ready to land your dream job?

Unlock your dream job with a 2-minute evaluator for a personalized learning plan!

Start Evaluator
Discover Your Strengths and Weaknesses: Take Our 2-Minute Quiz to Tailor Your Study Plan:
Question 1 out of 10

Problem: Given a list of tasks and a list of requirements, compute a sequence of tasks that can be performed, such that we complete every task once while satisfying all the requirements.

Which of the following method should we use to solve this problem?


Recommended Readings

Want a Structured Path to Master System Design Too? Don’t Miss This!


Load More