Convert Date Format - Practice Coding Problems

Convert Date Format - Problem

Database Easy

You are working with a database table called Days that stores date information. Your task is to transform each date from the standard database format into a human-readable string format.

The Days table has the following structure:

Column Name	Type
day	date

Each date in the day column should be converted to the format: "day_name, month_name day, year"

Example: 2019-01-15 should become "Tuesday, January 15, 2019"

Write a SQL query to return all dates in this human-friendly format. The result can be returned in any order.

Input & Output

example_1.sql — Basic Date Conversion

$ Input: Days table: +------------+ | day | +------------+ | 2019-01-15 | | 2020-02-01 | | 2021-08-20 | +------------+

› Output: +-----------------------------+ | day | +-----------------------------+ | Tuesday, January 15, 2019 | | Saturday, February 1, 2020 | | Friday, August 20, 2021 | +-----------------------------+

💡 Note: Each date is converted from YYYY-MM-DD format to 'Day_Name, Month_Name Day, Year' format. The day of week is calculated and month numbers are converted to full month names.

example_2.sql — Single Date

$ Input: Days table: +------------+ | day | +------------+ | 2022-12-25 | +------------+

› Output: +----------------------------+ | day | +----------------------------+ | Sunday, December 25, 2022 | +----------------------------+

💡 Note: Christmas Day 2022 falls on a Sunday. The date 2022-12-25 is correctly converted to the full text format with proper day name and month name.

example_3.sql — Leap Year Date

$ Input: Days table: +------------+ | day | +------------+ | 2020-02-29 | +------------+

› Output: +---------------------------+ | day | +---------------------------+ | Saturday, February 29, 2020 | +---------------------------+

💡 Note: The leap day of 2020 (February 29) is correctly handled. This demonstrates that the date formatting functions properly handle special dates like leap year days.

Visualization

Tap to expand

Understanding the Visualization

Input Processing

Database reads standard date format (YYYY-MM-DD)

Function Application

Built-in date formatting function processes the date

Component Extraction

Function extracts day name, month name, day number, and year

Format Assembly

Components are assembled into final human-readable format

Key Takeaway

🎯 Key Insight: Use built-in database date formatting functions for optimal performance and code simplicity. Each major database provides optimized functions specifically designed for this type of transformation.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Linear time with very low constant factor due to optimized built-in functions

✓ Linear Growth

Space Complexity

O(1)

Constant extra space per row

✓ Linear Space

Constraints

The table contains valid dates only
Date range can span multiple years
Results can be returned in any order
Date format follows standard SQL date format (YYYY-MM-DD)

Asked in

G Google 15 a Amazon 12 f Meta 8 ⊞ Microsoft 10

The optimal solution uses database-specific date formatting functions like DATE_FORMAT() in MySQL, TO_CHAR() in PostgreSQL, or FORMAT() in SQL Server. These built-in functions are highly optimized and provide clean, readable code compared to manual date component extraction.

Common Approaches

Approach	Time	Space	Notes
✓ Built-in Date Formatting (Optimal)	O(n)	O(1)	Use database-specific date formatting functions for efficient conversion
Manual Date Component Extraction	O(n)	O(1)	Extract each date component separately and concatenate manually

Built-in Date Formatting (Optimal) — Algorithm Steps

Identify the appropriate date formatting function for your SQL database
Use the correct format string pattern
Apply the function directly to the date column
Return the formatted result

Visualization

Tap to expand

Step-by-Step Walkthrough

Input Date

Database receives standard date format

Built-in Function

Apply DATE_FORMAT or equivalent with pattern

Formatted Output

Get human-readable string directly

Code -

solution.c — C

-- Universal Solution (works across most databases)
SELECT 
    CASE DAYOFWEEK(day)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END || ', ' ||
    CASE MONTH(day)
        WHEN 1 THEN 'January' WHEN 2 THEN 'February'
        WHEN 3 THEN 'March' WHEN 4 THEN 'April'
        WHEN 5 THEN 'May' WHEN 6 THEN 'June'
        WHEN 7 THEN 'July' WHEN 8 THEN 'August'
        WHEN 9 THEN 'September' WHEN 10 THEN 'October'
        WHEN 11 THEN 'November' WHEN 12 THEN 'December'
    END || ' ' || DAY(day) || ', ' || YEAR(day) AS day
FROM Days;

Time & Space Complexity

Time Complexity

⏱️

O(n)

Linear time with very low constant factor due to optimized built-in functions

✓ Linear Growth

Space Complexity

O(1)

Constant extra space per row

✓ Linear Space

Constraints

The table contains valid dates only
Date range can span multiple years
Results can be returned in any order
Date format follows standard SQL date format (YYYY-MM-DD)

23.5K Views

Medium Frequency

~8 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Built-in Date Formatting (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler