SQL Date Formatting with DATE_FORMAT

In many applications, dates need to be displayed in a human-readable and consistent format. SQL databases often store dates in a standard internal format, but for presentation purposes, you'll need to convert them into various user-friendly strings. This challenge focuses on using a SQL function similar to DATE_FORMAT to achieve this.

Problem Description

Your task is to write a SQL query that retrieves a list of products along with their creation dates, formatted according to specific patterns. You will be working with a Products table that contains product information, including a created_at timestamp. You need to select the product name and its creation date, but the date must be presented in two different string formats.

Key Requirements:

• Retrieve the product_name from the Products table.
• Retrieve the created_at timestamp from the Products table.
• Format the created_at timestamp into two distinct string representations:
  • 'YYYY-MM-DD' (e.g., '2023-10-27')
  • 'Day, Month DD, YYYY' (e.g., 'Friday, October 27, 2023')
• Alias the formatted date columns appropriately.

Expected Behavior:

The query should return rows where each row contains the product name, the first formatted date string, and the second formatted date string.

Edge Cases:

• Consider how dates without specific times (e.g., only a date part) should be handled by the formatting function. (For this challenge, assume created_at will always be a full timestamp).
• Ensure the formatting function correctly handles single-digit months and days by padding them with a leading zero.

Examples

Example 1:

Input Table: Products
| product_id | product_name | created_at             |
|------------|--------------|------------------------|
| 1          | Laptop       | 2023-10-26 10:30:00    |
| 2          | Keyboard     | 2023-10-27 14:00:00    |

Output:
| product_name | formatted_date_ymd | formatted_date_verbose |
|--------------|--------------------|------------------------|
| Laptop       | 2023-10-26         | Thursday, October 26, 2023 |
| Keyboard     | 2023-10-27         | Friday, October 27, 2023   |

Explanation:
The `created_at` for 'Laptop' (2023-10-26 10:30:00) is formatted as 'YYYY-MM-DD' to '2023-10-26' and in the verbose format to 'Thursday, October 26, 2023'.
The `created_at` for 'Keyboard' (2023-10-27 14:00:00) is formatted as 'YYYY-MM-DD' to '2023-10-27' and in the verbose format to 'Friday, October 27, 2023'.

Example 2:

Input Table: Products
| product_id | product_name | created_at             |
|------------|--------------|------------------------|
| 3          | Mouse        | 2023-11-01 08:00:00    |

Output:
| product_name | formatted_date_ymd | formatted_date_verbose |
|--------------|--------------------|------------------------|
| Mouse        | 2023-11-01         | Wednesday, November 01, 2023 |

Explanation:
The `created_at` for 'Mouse' (2023-11-01 08:00:00) is formatted to '2023-11-01' and 'Wednesday, November 01, 2023'. Note the leading zero for the day.

Constraints

• The Products table will contain at least one row.
• The created_at column will always contain valid timestamp values.
• The query should be efficient and not perform unnecessary operations.
• Assume you have access to a DATE_FORMAT(date_value, format_string) function in your SQL dialect. Common format specifiers include:
  • %Y: Four-digit year (e.g., 2023)
  • %m: Month as a decimal number (01-12)
  • %d: Day of the month as a decimal number (01-31)
  • %W: Full name of the weekday (e.g., Sunday)
  • %M: Full name of the month (e.g., January)

Notes

• This challenge simulates the common DATE_FORMAT function found in databases like MySQL. Other SQL dialects might use different functions or syntax (e.g., TO_CHAR in PostgreSQL/Oracle, FORMAT in SQL Server). Focus on understanding the concept of date formatting with specifiers.
• Pay close attention to the exact format strings required for the output.
• Think about how to combine literal strings (like commas and spaces) with format specifiers within the format_string argument of the DATE_FORMAT function.