Formatting and Parsing Dates

Learning Focus

Use this lesson to understand DATE_FORMAT() and STR_TO_DATE() — the two functions that convert between human-readable date strings and MySQL's internal date/time values.

Concept Overview

What Is Date Formatting and Parsing?

MySQL stores dates internally as structured values (e.g., 2026-02-10 14:30:00). But in real applications, you constantly need to:

• Format dates for display — show "February 10, 2026" instead of 2026-02-10
• Parse date strings from external sources — convert "10/02/2026" from a CSV file into a proper MySQL DATE

These two operations are handled by:

• DATE_FORMAT(date, format) — converts a date to a string
• STR_TO_DATE(string, format) — converts a string into a date

Why Is This Important?

Real-world data is messy. Dates arrive in dozens of formats:

• 2026-02-10 (ISO standard)
• 10/02/2026 (European DD/MM/YYYY)
• 02/10/2026 (American MM/DD/YYYY)
• Feb 10, 2026 (human-readable)

If you don't parse correctly, you get silent data corruption — February 10 becomes October 2, and nobody notices until reporting breaks.

---

Basic Syntax & Rules

Format Specifiers Reference

Both DATE_FORMAT() and STR_TO_DATE() use the same format tokens:

Token	Meaning	Example
%Y	4-digit year	2026
%y	2-digit year	26
%m	Month, zero-padded	02
%c	Month, no padding	2
%M	Full month name	February
%b	Abbreviated month	Feb
%d	Day, zero-padded	10
%e	Day, no padding	10
%H	Hour (24-hour), zero-padded	14
%h	Hour (12-hour), zero-padded	02
%i	Minutes, zero-padded	30
%s	Seconds, zero-padded	00
%p	AM/PM	PM
%W	Full weekday name	Tuesday
%a	Abbreviated weekday	Tue

DATE_FORMAT — Date to String

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')         AS iso_date,
       DATE_FORMAT(NOW(), '%d/%m/%Y')         AS european,
       DATE_FORMAT(NOW(), '%M %d, %Y')        AS human_readable,
       DATE_FORMAT(NOW(), '%W, %M %e, %Y')    AS full_display,
       DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS full_datetime;

iso_date	european	human_readable	full_display	full_datetime
2026-02-10	10/02/2026	February 10, 2026	Tuesday, February 10, 2026	2026-02-10 14:30:00

STR_TO_DATE — String to Date

SELECT STR_TO_DATE('10/02/2026', '%d/%m/%Y')         AS from_european,
       STR_TO_DATE('Feb 10, 2026', '%b %d, %Y')      AS from_short,
       STR_TO_DATE('2026-02-10 14:30', '%Y-%m-%d %H:%i') AS from_datetime;

from_european	from_short	from_datetime
2026-02-10	2026-02-10	2026-02-10 14:30:00

DATE() — Extract Date from DateTime

SELECT DATE('2026-02-10 14:30:00') AS date_only;
-- Result: 2026-02-10

---

Step-by-Step Examples

Example 1: Formatting Orders for a Report

Scenario: Your report needs dates in "Month DD, YYYY" format.

Step 1: Raw data

SELECT order_id, total_amount, order_date FROM orders LIMIT 3;

order_id	total_amount	order_date
1001	250.00	2026-02-08 09:15:00
1002	180.50	2026-02-09 11:30:00
1003	420.00	2026-02-10 14:00:00

Step 2: Format for the report

SELECT order_id,
       total_amount,
       DATE_FORMAT(order_date, '%M %d, %Y') AS formatted_date,
       DATE_FORMAT(order_date, '%h:%i %p')  AS formatted_time
FROM orders
LIMIT 3;

order_id	total_amount	formatted_date	formatted_time
1001	250.00	February 08, 2026	09:15 AM
1002	180.50	February 09, 2026	11:30 AM
1003	420.00	February 10, 2026	02:00 PM

---

Example 2: Parsing CSV Import Data

Scenario: You receive a CSV where dates are in European format (DD/MM/YYYY). You need to insert them into a DATE column.

Step 1: Understand the incoming format

order_ref,customer,order_date,amount
REF001,Alice,15/01/2026,300.00
REF002,Bob,28/01/2026,150.00
REF003,Charlie,03/02/2026,500.00

Step 2: Parse during insert

INSERT INTO orders (order_ref, customer_name, order_date, total_amount)
VALUES
    ('REF001', 'Alice',   STR_TO_DATE('15/01/2026', '%d/%m/%Y'), 300.00),
    ('REF002', 'Bob',     STR_TO_DATE('28/01/2026', '%d/%m/%Y'), 150.00),
    ('REF003', 'Charlie', STR_TO_DATE('03/02/2026', '%d/%m/%Y'), 500.00);

Step 3: Verify the parsed dates

SELECT order_ref, order_date FROM orders;

order_ref	order_date
REF001	2026-01-15
REF002	2026-01-28
REF003	2026-02-03

---

Example 3: Detecting Parse Failures

When STR_TO_DATE() fails to parse, it returns NULL silently. This is dangerous.

SELECT STR_TO_DATE('31/02/2026', '%d/%m/%Y') AS bad_date;
-- Result: NULL (February 31 doesn't exist!)

SELECT STR_TO_DATE('not-a-date', '%Y-%m-%d') AS broken;
-- Result: NULL

How to catch parse failures:

-- Find rows where parsing failed during import
SELECT *
FROM import_staging
WHERE STR_TO_DATE(raw_date_column, '%d/%m/%Y') IS NULL
  AND raw_date_column IS NOT NULL;

---

Example 4: GROUP BY Month with Formatting

SELECT
    DATE_FORMAT(order_date, '%Y-%m')   AS month,
    DATE_FORMAT(order_date, '%M %Y')   AS month_label,
    COUNT(*)                           AS order_count,
    SUM(total_amount)                  AS revenue
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m'),
         DATE_FORMAT(order_date, '%M %Y')
ORDER BY month;

month	month_label	order_count	revenue
2026-01	January 2026	150	45000.00
2026-02	February 2026	89	28500.00

---

Practical Use Cases

1. Report Generation

Format dates for human-readable reports, invoices, and email notifications.

2. Data Import/ETL Pipelines

Parse dates from CSV, JSON, or API responses where the format doesn't match MySQL's YYYY-MM-DD.

3. Localized Display

Different regions expect different formats — DD/MM/YYYY in Europe, MM/DD/YYYY in the US. Format server-side or in the app.

4. Log Analysis

Group events by hour, day, or month using DATE_FORMAT() for time-based aggregation.

5. API Responses

Convert internal datetime values to ISO 8601 format (%Y-%m-%dT%H:%i:%sZ) for JSON APIs.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Wrong format tokens in STR_TO_DATE()	Returns NULL (silent failure)	Test with sample rows; check month/day order carefully
Confusing %m (month) with %i (minutes)	Parsed values are completely wrong	Remember: %m = month, %i = minutes
Using DATE_FORMAT() in WHERE clauses	Applies function to every row — bypasses index	Filter on raw date columns, format only in SELECT
European vs. American date ambiguity	01/02/2026 — is that Jan 2 or Feb 1?	Always document the expected format; validate on import
Not handling NULL results from STR_TO_DATE()	Bad data silently enters the database	Check for NULLs after parsing and reject invalid rows
Using 2-digit years (%y)	Year 2000 ambiguity	Always use 4-digit years (%Y)

---

Best Practices

• Store dates in canonical format — MySQL's native YYYY-MM-DD / YYYY-MM-DD HH:MM:SS
• Format only at output time — keep raw dates in storage, format in the SELECT clause
• Never format in WHERE clauses — it prevents index usage
• Validate before importing — test STR_TO_DATE() on sample rows before bulk loading
• Use 4-digit years — %Y not %y to avoid ambiguity
• Document expected input formats — especially when importing from external systems

---

Hands-On Practice

Exercise 1: Format for Display (Easy)

Write a query that shows all orders with:

• order_id
• order_date formatted as "Tue, 10 Feb 2026"
• order_date formatted as "2:30 PM"

Exercise 2: Parse External Data (Medium)

You receive dates in the format "February 10, 2026 2:30 PM". Write a STR_TO_DATE() expression that converts this to a DATETIME. Then write a validation query to find any rows where parsing returns NULL.

Exercise 3: Monthly Revenue Report (Advanced)

Write a query that:

1. Groups orders by month (using DATE_FORMAT)
2. Shows the month as "January 2026" (human-friendly)
3. Shows total_orders, total_revenue, and avg_order_value
4. Only includes the last 6 months
5. Orders by most recent month first

---

Connection to Other Concepts

Related Concept	How It Connects
Current Date/Time Functions	NOW() returns the value you often format with DATE_FORMAT()
Date Arithmetic	Add intervals to dates and format the result
Indexes	DATE_FORMAT() in WHERE clauses breaks index usage — use range scans instead
Data Import	STR_TO_DATE() is essential for ETL pipelines and CSV imports
Reporting Views	Create views with pre-formatted dates for dashboard queries

What to Learn Next

• Date Arithmetic and Difference — calculate intervals between dates

---

Visual Learning Diagram

flowchart LR
    A["Raw Date\n2026-02-10 14:30:00"] --> B["DATE_FORMAT()"]
    B --> C["Display String\n'February 10, 2026'"]

    D["Input String\n'10/02/2026'"] --> E["STR_TO_DATE()"]
    E --> F["MySQL DATE\n2026-02-10"]

    G["DateTime Column"] --> H["DATE()"]
    H --> I["Date Only\n2026-02-10"]

    classDef func fill:#0d6efd,stroke:#fff,color:#fff
    class B,E,H func

Quick Reference

-- Format date for display
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i');

-- Parse string to date
SELECT STR_TO_DATE('10/02/2026', '%d/%m/%Y');
SELECT STR_TO_DATE('Feb 10, 2026', '%b %d, %Y');

-- Extract date from datetime
SELECT DATE(NOW());
SELECT DATE(created_at) FROM orders;

-- Extract parts
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());

What's Next

• Date Arithmetic and Difference