Eric Campbell 5 min read Engineering

SQL - FORMAT() / TO_CHAR() - Format Dates

Raw date output from databases rarely matches what users expect to see. A timestamp like `2024-03-15 14:30:22.000` means nothing to a business user scanning a report. They want 'March 15, 2024' or...

Key Insights

  • SQL Server’s FORMAT() uses .NET format strings while PostgreSQL/Oracle’s TO_CHAR() uses format model elements—knowing both syntaxes is essential for cross-database work
  • Date formatting in SQL is convenient for reports and exports, but formatting in the application layer often provides better performance and flexibility
  • Never use formatted date strings in WHERE clauses; always filter on raw date columns to preserve index usage

Why Date Formatting Matters

Raw date output from databases rarely matches what users expect to see. A timestamp like 2024-03-15 14:30:22.000 means nothing to a business user scanning a report. They want “March 15, 2024” or “15/03/2024” depending on their locale.

Date formatting functions transform these raw values into human-readable strings. The two dominant approaches are SQL Server’s FORMAT() function and the TO_CHAR() function found in PostgreSQL and Oracle. Understanding both is essential if you work across database platforms.

FORMAT() Function (SQL Server)

SQL Server introduced FORMAT() in version 2012. It wraps .NET’s formatting capabilities, giving you access to standard and custom format strings.

The basic syntax is straightforward:

FORMAT(value, format_string [, culture])

Here’s how it works with common patterns:

DECLARE @date DATETIME = '2024-03-15 14:30:22';

-- Standard .NET format strings
SELECT FORMAT(@date, 'd') AS ShortDate;        -- 3/15/2024
SELECT FORMAT(@date, 'D') AS LongDate;         -- Friday, March 15, 2024
SELECT FORMAT(@date, 'f') AS FullShort;        -- Friday, March 15, 2024 2:30 PM
SELECT FORMAT(@date, 'F') AS FullLong;         -- Friday, March 15, 2024 2:30:22 PM

Custom format strings give you precise control:

DECLARE @date DATETIME = '2024-03-15 14:30:22';

SELECT FORMAT(@date, 'MM/dd/yyyy') AS USFormat;           -- 03/15/2024
SELECT FORMAT(@date, 'dd-MM-yyyy') AS EuroFormat;         -- 15-03-2024
SELECT FORMAT(@date, 'yyyy-MM-dd') AS ISOFormat;          -- 2024-03-15
SELECT FORMAT(@date, 'MMMM dd, yyyy') AS Written;         -- March 15, 2024
SELECT FORMAT(@date, 'ddd, MMM d') AS Abbreviated;        -- Fri, Mar 15
SELECT FORMAT(@date, 'HH:mm:ss') AS Time24;               -- 14:30:22
SELECT FORMAT(@date, 'hh:mm tt') AS Time12;               -- 02:30 PM

The format specifiers follow a consistent pattern: lowercase for shorter output, uppercase or repeated characters for longer output. M gives you the month number, MM pads it with zeros, MMM gives the abbreviated name, and MMMM gives the full name.

Locale support is built in through the optional culture parameter:

DECLARE @date DATETIME = '2024-03-15';

SELECT FORMAT(@date, 'D', 'en-US') AS English;    -- Friday, March 15, 2024
SELECT FORMAT(@date, 'D', 'de-DE') AS German;     -- Freitag, 15. März 2024
SELECT FORMAT(@date, 'D', 'fr-FR') AS French;     -- vendredi 15 mars 2024
SELECT FORMAT(@date, 'D', 'ja-JP') AS Japanese;   -- 2024年3月15日金曜日

TO_CHAR() Function (PostgreSQL/Oracle)

PostgreSQL and Oracle use TO_CHAR() with format model elements instead of .NET format strings. The syntax differs, but the concept is identical:

TO_CHAR(timestamp, format_mask)

Here’s the PostgreSQL equivalent of our earlier examples:

-- PostgreSQL examples
SELECT TO_CHAR(NOW(), 'MM/DD/YYYY') AS us_format;          -- 03/15/2024
SELECT TO_CHAR(NOW(), 'DD-MM-YYYY') AS euro_format;        -- 15-03-2024
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS iso_format;         -- 2024-03-15
SELECT TO_CHAR(NOW(), 'Month DD, YYYY') AS written;        -- March    15, 2024
SELECT TO_CHAR(NOW(), 'Dy, Mon DD') AS abbreviated;        -- Fri, Mar 15
SELECT TO_CHAR(NOW(), 'HH24:MI:SS') AS time_24;            -- 14:30:22
SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS time_12;            -- 02:30 PM

Notice the format elements are different. YYYY instead of yyyy, DD instead of dd, Month instead of MMMM. The casing of the format element affects the output casing in PostgreSQL:

SELECT TO_CHAR(NOW(), 'MONTH') AS upper_month;    -- MARCH
SELECT TO_CHAR(NOW(), 'Month') AS title_month;    -- March
SELECT TO_CHAR(NOW(), 'month') AS lower_month;    -- march

One quirk: Month and Day pad output to fixed widths for alignment. Use FM (fill mode) to suppress padding:

SELECT TO_CHAR(NOW(), 'FMMonth DD, YYYY') AS clean;  -- March 15, 2024

Common Date Format Patterns

Here’s a reference table showing equivalent patterns across both systems:

Purpose SQL Server FORMAT() PostgreSQL TO_CHAR()
ISO 8601 Date yyyy-MM-dd YYYY-MM-DD
US Short Date MM/dd/yyyy MM/DD/YYYY
European Date dd/MM/yyyy DD/MM/YYYY
Full Month Name MMMM FMMonth
Abbreviated Month MMM Mon
Day of Week dddd FMDay
24-Hour Time HH:mm:ss HH24:MI:SS
12-Hour Time hh:mm tt HH12:MI AM
ISO 8601 Timestamp yyyy-MM-ddTHH:mm:ss YYYY-MM-DD"T"HH24:MI:SS
Week Number ww WW
Quarter q Q

Side-by-side comparison for a complete timestamp:

-- SQL Server
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff');
-- Output: 2024-03-15 14:30:22.123

-- PostgreSQL
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.MS');
-- Output: 2024-03-15 14:30:22.123

Handling Locales and Internationalization

Regional date formatting goes beyond pattern differences. Month and day names need translation, and date component ordering varies by culture.

In SQL Server, the culture parameter handles everything:

DECLARE @date DATETIME = '2024-03-15';

-- Different regional formats
SELECT FORMAT(@date, 'd', 'en-US') AS US;      -- 3/15/2024
SELECT FORMAT(@date, 'd', 'en-GB') AS UK;      -- 15/03/2024
SELECT FORMAT(@date, 'd', 'de-DE') AS Germany; -- 15.03.2024
SELECT FORMAT(@date, 'd', 'zh-CN') AS China;   -- 2024/3/15

PostgreSQL handles this through session settings:

-- Set locale for date output
SET lc_time = 'de_DE.UTF-8';
SELECT TO_CHAR(NOW(), 'TMDay, DD. TMMonth YYYY');
-- Output: Freitag, 15. März 2024

SET lc_time = 'fr_FR.UTF-8';
SELECT TO_CHAR(NOW(), 'TMDay DD TMMonth YYYY');
-- Output: vendredi 15 mars 2024

The TM prefix (translation mode) tells PostgreSQL to use the session’s locale for month and day names.

Practical Use Cases

Date formatting shines in reporting scenarios. Here’s a sales summary grouped by month:

-- SQL Server: Monthly sales report
SELECT 
    FORMAT(OrderDate, 'MMMM yyyy') AS SalesMonth,
    COUNT(*) AS OrderCount,
    FORMAT(SUM(TotalAmount), 'C') AS Revenue
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY FORMAT(OrderDate, 'MMMM yyyy'), YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate);

-- PostgreSQL: Same report
SELECT 
    TO_CHAR(order_date, 'FMMonth YYYY') AS sales_month,
    COUNT(*) AS order_count,
    TO_CHAR(SUM(total_amount), 'FM$999,999,990.00') AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY TO_CHAR(order_date, 'FMMonth YYYY'), 
         DATE_TRUNC('month', order_date)
ORDER BY DATE_TRUNC('month', order_date);

For API responses or log entries, ISO 8601 format is standard:

-- SQL Server: Audit log entry
SELECT 
    FORMAT(CreatedAt, 'yyyy-MM-ddTHH:mm:ss.fffZ') AS Timestamp,
    UserName,
    Action
FROM AuditLog
WHERE CreatedAt >= DATEADD(HOUR, -1, GETUTCDATE());

-- PostgreSQL: Same query
SELECT 
    TO_CHAR(created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS timestamp,
    user_name,
    action
FROM audit_log
WHERE created_at >= NOW() - INTERVAL '1 hour';

Performance Considerations and Best Practices

Date formatting functions carry overhead. FORMAT() in SQL Server is particularly expensive because it invokes the CLR. For large result sets, this adds up.

Never filter on formatted dates:

-- BAD: This prevents index usage
SELECT * FROM Orders 
WHERE FORMAT(OrderDate, 'yyyy-MM') = '2024-03';

-- GOOD: Filter on the raw column
SELECT * FROM Orders 
WHERE OrderDate >= '2024-03-01' 
  AND OrderDate < '2024-04-01';

Format only what you display:

-- BAD: Formatting millions of rows
SELECT FORMAT(CreatedAt, 'yyyy-MM-dd HH:mm:ss') AS Timestamp
FROM LargeLogTable;

-- BETTER: Let the application handle formatting
SELECT CreatedAt FROM LargeLogTable;

Consider computed columns for frequent patterns:

-- SQL Server: Persisted computed column
ALTER TABLE Orders
ADD OrderMonth AS FORMAT(OrderDate, 'MMMM yyyy') PERSISTED;

-- PostgreSQL: Generated column
ALTER TABLE orders
ADD COLUMN order_month TEXT 
GENERATED ALWAYS AS (TO_CHAR(order_date, 'FMMonth YYYY')) STORED;

My general rule: format dates in SQL for exports, reports, and ad-hoc queries. Format in the application layer for user interfaces and APIs where you need flexibility and performance.

For cross-database applications, consider abstracting date formatting into views or stored procedures. This isolates the platform-specific syntax and makes your application code cleaner.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.