Formatting Dates with DATE_FORMAT in SQL

Date formatting is a common requirement in data analysis and reporting. This challenge asks you to write a SQL query that utilizes the DATE_FORMAT function to transform dates into a specific string format. Mastering this function allows you to present date information in a user-friendly and consistent manner.

Problem Description

You are given a table named orders with a column named order_date of type DATE. Your task is to write a SQL query that selects the order_date column and formats it using the DATE_FORMAT function. The desired output format is YYYY-MM-DD. The query should return a result set with a single column named formatted_date containing the formatted dates.

Key Requirements:

• Use the DATE_FORMAT function.
• Format the order_date column as YYYY-MM-DD.
• Rename the resulting column to formatted_date.
• Handle all valid date values in the order_date column.

Expected Behavior:

The query should return a table with a single column, formatted_date, where each row contains the corresponding order_date value formatted as YYYY-MM-DD.

Edge Cases to Consider:

• The order_date column might contain NULL values. The DATE_FORMAT function will return NULL if the input date is NULL. This behavior is acceptable.
• The order_date column contains valid dates.

Examples

Example 1:

Input:
orders table:
order_id | order_date
------- | --------
1        | 2023-10-26
2        | 2023-11-15
3        | 2023-12-01

Output:
formatted_date
--------------
2023-10-26
2023-11-15
2023-12-01

Explanation: The order_date values are formatted as YYYY-MM-DD and the column is renamed to formatted_date.

Example 2:

Input:
orders table:
order_id | order_date
------- | --------
1        | 2024-01-05
2        | NULL
3        | 2024-02-29

Output:
formatted_date
--------------
2024-01-05
NULL
2024-02-29

Explanation: The order_date values are formatted as YYYY-MM-DD. The NULL value is correctly handled and returned as NULL.

Constraints

• The order_date column will contain only valid DATE values or NULL.
• The database system is assumed to support the standard SQL DATE_FORMAT function.
• The query should be efficient and return the results in a reasonable time.

Notes

• The DATE_FORMAT function's syntax might vary slightly depending on the specific database system (e.g., MySQL, PostgreSQL, SQL Server). However, the general format specifiers (YYYY, MM, DD) are commonly supported.
• Consider the potential for different date formats and how the DATE_FORMAT function can be used to achieve the desired output.
• Focus on clarity and correctness in your SQL query.

Pseudocode:

SELECT
    DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM
    orders;