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

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

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

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

Hint

Use ORDER BY signup_date at the end of the query.

Practice

1. What does the MySQL function DATE_FORMAT(date, format) do?

easy

A. It calculates the difference between two dates.

B. It converts a string to a date type.

C. It adds days to a date value.

D. It changes how a date or time is displayed using a specific pattern.

Solution

1. Step 1: Understand the function purpose

   The DATE_FORMAT function is used to display dates in a specific format by applying a pattern string.

2. Step 2: Compare options with function behavior

   Only It changes how a date or time is displayed using a specific pattern. correctly describes changing the display format of a date/time value.

3. Final Answer:

   It changes how a date or time is displayed using a specific pattern. -> Option D

4. Quick Check:

   DATE_FORMAT changes display format = A [OK]

Hint: DATE_FORMAT changes date display style, not date value [OK]

Common Mistakes:

• Confusing DATE_FORMAT with date arithmetic functions
• Thinking it converts strings to dates
• Assuming it calculates date differences

2. Which of the following is the correct syntax to format a date as 'Year-Month-Day' using DATE_FORMAT?

easy

A. DATE_FORMAT(date, '%Y-%m-%d')

B. DATE_FORMAT(date, 'YYYY-MM-DD')

C. DATE_FORMAT(date, '%y/%M/%d')

D. DATE_FORMAT(date, 'YY-MM-DD')

Solution

1. Step 1: Recall correct format specifiers

   In MySQL, '%Y' is 4-digit year, '%m' is 2-digit month, '%d' is 2-digit day.

2. Step 2: Check each option's format string

   Only DATE_FORMAT(date, '%Y-%m-%d') uses correct MySQL format specifiers and separators for 'Year-Month-Day'. Others use invalid or wrong specifiers.

3. Final Answer:

   DATE_FORMAT(date, '%Y-%m-%d') -> Option A

4. Quick Check:

   Use %Y-%m-%d for year-month-day [OK]

Hint: Use %Y for year, %m for month, %d for day in DATE_FORMAT [OK]

Common Mistakes:

• Using uppercase YYYY instead of %Y
• Mixing format codes from other languages
• Using wrong separators like slashes instead of dashes

3. Given the query:
SELECT DATE_FORMAT('2024-06-15 14:30:00', '%W, %M %d, %Y %r') AS formatted_date;
What is the expected output in the formatted_date column?

medium

A. Sun, Jun 15, 2024 02:30 PM

B. Saturday, June 15, 2024 02:30:00 PM

C. Saturday, June 15, 2024 14:30:00

D. 2024-06-15 14:30:00

Solution

1. Step 1: Understand each format specifier

   %W = full weekday name (Saturday), %M = full month name (June), %d = day (15), %Y = year (2024), %r = 12-hour time with seconds and AM/PM (02:30:00 PM).

2. Step 2: Apply format to given datetime

   The datetime '2024-06-15 14:30:00' is 2:30 PM in 12-hour format, so output matches Saturday, June 15, 2024 02:30:00 PM exactly.

3. Final Answer:

   Saturday, June 15, 2024 02:30:00 PM -> Option B

4. Quick Check:

   Full weekday, month, and 12-hour time = C [OK]

Hint: Remember %r gives 12-hour time with seconds and AM/PM [OK]

Common Mistakes:

• Confusing %r with 24-hour time %H:%i:%s
• Using abbreviated weekday (%a) instead of full (%W)
• Ignoring AM/PM in output

4. Identify the error in this query:
SELECT DATE_FORMAT('2024-06-15', '%Y-%m-%d %H:%i:%s') AS formatted_date;
What will happen when this query runs?

medium

A. It will return NULL because the input has no time part.

B. It will cause a syntax error due to wrong format specifiers.

C. It will return '2024-06-15 00:00:00' as the time defaults to midnight.

D. It will return only the date part ignoring the time format.

Solution

1. Step 1: Analyze input and format

   The input date '2024-06-15' has no time, but MySQL treats missing time as '00:00:00'. Format includes time specifiers %H:%i:%s.

2. Step 2: Predict output behavior

   MySQL fills missing time with zeros, so output will be '2024-06-15 00:00:00'. No syntax error occurs.

3. Final Answer:

   It will return '2024-06-15 00:00:00' as the time defaults to midnight. -> Option C

4. Quick Check:

   Missing time defaults to 00:00:00 in DATE_FORMAT [OK]

Hint: Missing time defaults to 00:00:00, no error [OK]

Common Mistakes:

• Expecting syntax error for valid format
• Assuming NULL output for missing time
• Ignoring default midnight time fill

5. You want to display a datetime column created_at as 'Day-Month-Year Hour:Minute AM/PM' in a report. Which DATE_FORMAT pattern correctly formats it as '15-Jun-2024 02:30 PM'?

hard

A. DATE_FORMAT(created_at, '%d-%b-%Y %h:%i %p')

B. DATE_FORMAT(created_at, '%d-%m-%Y %H:%i %p')

C. DATE_FORMAT(created_at, '%d-%M-%Y %h:%i %P')

D. DATE_FORMAT(created_at, '%d-%b-%Y %H:%i %p')

Solution

1. Step 1: Match each format code to desired output

   %d = day with leading zero (15), %b = abbreviated month name (Jun), %Y = 4-digit year (2024), %h = 12-hour hour (02), %i = minutes (30), %p = AM/PM uppercase.

2. Step 2: Check options for correct codes and case

   DATE_FORMAT(created_at, '%d-%b-%Y %h:%i %p') matches all codes correctly. DATE_FORMAT(created_at, '%d-%m-%Y %H:%i %p') uses %m (numeric month) instead of %b. DATE_FORMAT(created_at, '%d-%M-%Y %h:%i %P') uses %M (full month) and %P (invalid). DATE_FORMAT(created_at, '%d-%b-%Y %H:%i %p') uses %H (24-hour) instead of %h.

3. Final Answer:

   DATE_FORMAT(created_at, '%d-%b-%Y %h:%i %p') -> Option A

4. Quick Check:

   Use %b for short month, %h for 12-hour, %p for AM/PM [OK]

Hint: Use %b for short month, %h for 12-hour, %p for AM/PM [OK]

Common Mistakes:

• Using %m instead of %b for month name
• Using %H (24-hour) instead of %h (12-hour)
• Using %P which is invalid in MySQL