How to Format Dates in SQL Server

Why would I format dates in SQL Server?

Applications, reports, and APIs often need dates as strings like “2024-03-15” or “Mar 15, 2024”. SQL Server lets you transform DATE, DATETIME, and DATETIME2 values into these readable formats without changing the stored data.

How do I format a date with CONVERT()?

CONVERT(data_type, expression, style) casts a date to VARCHAR and applies a numeric style code (0-150). Use VARCHAR(30) to avoid truncation.

SELECT CONVERT(VARCHAR(10), order_date, 23) AS iso_date -- 2024-03-15
FROM Orders;

Which style codes matter most?

Style 23 (YYYY-MM-DD), 101 (MM/DD/YYYY), 103 (DD/MM/YYYY), and 113 (DD Mon YYYY HH:MI:SS:MMM) cover most reporting needs.

How do I format a date with FORMAT()?

FORMAT(value, .NET_format [, culture]) applies .NET custom format strings, giving more flexibility but slightly slower performance.

SELECT FORMAT(order_date,'MMMM dd, yyyy','en-US') AS long_us_date
FROM Orders;

Can I include time zones?

FORMAT() handles datetimeoffset: FORMAT(ship_time,'yyyy-MM-dd HH:mm zzz').

When should I use style codes vs .NET format strings?

Use CONVERT for speed and common styles. Use FORMAT for custom layouts, localization, or datetimeoffset formatting.

How do I embed formatted dates in ecommerce queries?

SELECT o.id,
c.name,
CONVERT(VARCHAR(10), o.order_date, 23) AS order_date_iso,
FORMAT(o.order_date,'dd MMM yy') AS order_date_short,
o.total_amount
FROM Orders o
JOIN Customers c ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01';

What are best practices for date formatting in reports?

Store dates as DATE/DATETIME2, format only in the SELECT layer, keep ISO 8601 for APIs, and always specify culture when using FORMAT.

Common mistakes and how to avoid them

Using VARCHAR(8) and truncating data

VARCHAR(8) cuts off longer formats—use VARCHAR(30) or let SQL Server size automatically.

Assuming client locale

FORMAT() defaults to server culture, which may differ from users. Pass an explicit culture code like ‘en-US’.

FAQs

Does FORMAT() work in SQL Server 2008?

No. FORMAT was introduced in SQL Server 2012. Prior versions must use CONVERT or CLR functions.

Is there a performance hit with FORMAT()?

Yes—about 2-3× slower than CONVERT because it relies on .NET. Cache results or apply formatting in the presentation layer for large datasets.