How to Format Date in SQL

Use date formatting when raw date or timestamp values need to appear in a human-readable layout for reports, exports, dashboards, or UI-friendly query output. The exact function depends on your SQL database, but the core pattern stays the same.

What you’ll build or solve

You’ll learn how to format dates in SQL using common database date-formatting functions. You’ll also know when formatting should stay in SQL versus the application layer.

When this approach works best

This approach is the right choice when query results are used directly in reports, exports, or admin tools.

Common real-world scenarios include:

• Dashboard labels
• CSV exports
• Invoice dates
• Audit logs
• BI reporting views

This is a bad idea when the frontend already handles localization and user-specific timezone display better.

Prerequisites

You only need:

• A SQL table with DATE or TIMESTAMP columns
• Basic SELECT knowledge
• Awareness of your database engine

Step-by-step instructions

Step 1: Use your database’s date-format function

The exact function differs by database.

PostgreSQL

SELECT TO_CHAR(created_at, 'YYYY-MM-DD') AS formatted_date
FROM orders;

MySQL

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

SQL Server

SELECT FORMAT(created_at, 'yyyy-MM-dd') AS formatted_date
FROM orders;

These all produce a clean formatted date string.

What to look for:

• Formatting syntax is database-specific
• Output becomes text
• Great for exports and dashboards
• Keep raw timestamps for timezone-safe logic
• Frontend formatting may still be better for localization

Examples you can copy

Invoice date

SELECT TO_CHAR(invoice_date, 'Mon DD, YYYY')
FROM invoices;

Export-friendly date

SELECT DATE_FORMAT(created_at, '%Y-%m-%d')
FROM users;

Month label

SELECT TO_CHAR(created_at, 'YYYY-MM')
FROM reports;

Common mistakes and how to fix them

Mistake 1: Using the wrong database function

What the reader might do:

SELECT DATE_FORMAT(created_at, '%Y-%m-%d')

Why it breaks: this fails in PostgreSQL.

Corrected approach:

Use the function for your database engine.

Mistake 2: Formatting too early in query logic

What the reader might do:

ORDER BY TO_CHAR(created_at, 'MM-DD-YYYY')

Why it breaks: string sorting can misorder dates.

Corrected approach:

Sort by the raw date column, format only in the final output.

Mistake 3: Using SQL formatting for localized UI dates

What the reader might do:

Format directly in SQL for user-facing dashboards.

Why it breaks: localization and timezone handling are usually better in the application layer.

Corrected approach:

Return raw timestamps when the frontend owns display formatting.

Troubleshooting

If the syntax errors, verify the database-specific function.

If sorted dates look wrong, order by the raw date.

If localization matters, move formatting to the app layer.

If timestamps look shifted, confirm timezone conversion happens before formatting.

Quick recap

• Format dates with database-specific functions
• Output becomes text
• Great for reports and exports
• Sort by raw dates, not formatted strings
• Prefer frontend formatting for localized UI