SQL Server FORMAT() Function

The FORMAT() function in SQL Server provides a flexible way to format various data types, including dates, times, and numbers, for display purposes. It allows you to customize the output string based on specific formatting patterns and optionally specify a culture (language/region) for localized formatting.

FORMAT(): Definition and Usage

FORMAT() gives you fine-grained control over how your data appears. It's particularly useful for generating reports or displaying data in a user-friendly way. For general data type conversions (not just formatting), consider using CAST() or CONVERT() instead.

Syntax


FORMAT ( value, format [, culture ] )

Parameter Values

Parameter	Description
`value`	The value to be formatted (number, date, or time). This is required.
`format`	A string specifying the format pattern (see examples and documentation for details). This is required.
`culture` (Optional, SQL Server 2017 and later)	A culture name (e.g., 'en-US', 'no', 'de') to apply localized formatting. If omitted, the server's default culture is used.

Examples

Formatting a Date with Different Cultures

This shows how to format a date using different cultures to demonstrate localization.

Syntax


DECLARE @d DATETIME = '2018-12-01';
SELECT 
    FORMAT(@d, 'd', 'en-US') AS 'US English Result',
    FORMAT(@d, 'd', 'no') AS 'Norwegian Result',
    FORMAT(@d, 'd', 'zu') AS 'Zulu Result';

Output


US English Result | Norwegian Result | Zulu Result
-------------------------------------------------
12/1/2018         | 01.12.2018       | 2018/12/01

Formatting a Number

This demonstrates formatting a number using a custom pattern.

Syntax


SELECT FORMAT(123456789, '##-##-#####');

Output


12-34-56789