DATE_FORMAT function in MySQL - Mini Project: Build & Apply

Formatting Dates with DATE_FORMAT in MySQL

📖 Scenario: You work in a small company database team. Your manager wants to display customer signup dates in a friendlier format on the website.

🎯 Goal: Learn how to use the MySQL DATE_FORMAT function to change the appearance of date values in query results.

📋 What You'll Learn

Create a table called customers with columns id, name, and signup_date

Insert three customers with specific signup dates

Write a query selecting customer names and their signup dates formatted as 'Month day, Year' (e.g., 'January 5, 2023')

Use the DATE_FORMAT function with the correct format string

💡 Why This Matters

🌍 Real World

Formatting dates nicely is important for user-friendly reports, websites, and applications that show dates.

💼 Career

Database developers and analysts often need to format dates for reports and user interfaces using functions like DATE_FORMAT.

1

Create the customers table

Write a SQL statement to create a table called customers with these columns: id as an integer primary key, name as a VARCHAR(50), and signup_date as a DATE.

MySQL

-- Your code here to create the customers table

Need a hint?

Use CREATE TABLE with the exact column names and types.

2

Insert three customers with signup dates

Write SQL INSERT statements to add these customers to the customers table: (1, 'Alice', '2023-01-05'), (2, 'Bob', '2023-02-15'), and (3, 'Charlie', '2023-03-20').

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  signup_date DATE
);
-- Your code here to insert the three customers

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Write a query to select names and formatted signup dates

Write a SQL SELECT statement to get the name and the signup_date formatted as 'Month day, Year' using DATE_FORMAT(signup_date, '%M %e, %Y'). Name the formatted column formatted_date.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  signup_date DATE
);

INSERT INTO customers (id, name, signup_date) VALUES
(1, 'Alice', '2023-01-05'),
(2, 'Bob', '2023-02-15'),
(3, 'Charlie', '2023-03-20');

-- Your code here to select name and formatted signup_date

Need a hint?

Use DATE_FORMAT with the format string '%M %e, %Y' to get 'Month day, Year'.

4

Complete the query with ordering by signup date

Add an ORDER BY signup_date clause to the previous SELECT statement to show customers sorted by their signup date from earliest to latest.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  signup_date DATE
);

INSERT INTO customers (id, name, signup_date) VALUES
(1, 'Alice', '2023-01-05'),
(2, 'Bob', '2023-02-15'),
(3, 'Charlie', '2023-03-20');

SELECT name, DATE_FORMAT(signup_date, '%M %e, %Y') AS formatted_date FROM customers
-- Your code here to order by signup_date

Need a hint?

Use ORDER BY signup_date at the end of the query.