FORMAT (TSQL Function)

Returns a string representation of a numeric value or datetime value, formatted with the specified format and language.

For general data type conversions, use CAST or CONVERT.

Syntax

FORMAT ( numeric_expression , format_string [, language] )

FORMAT ( datetime_expression , format_string [, language] )

Arguments

numeric_expression

is a numeric expression to format. It is of type int, bigint, money, numeric, float.

datetime_expression

is a datetime expression to format. It is of type date, time, datetime.

format_string

is the format pattern, of type varchar.
See Numeric Format Strings and Datetime Format Strings for description of format string.

language:

optional argument, of type varchar.
If not specified, the current language is used (see SET LANGUAGE statement).

Return Types

Returns varchar.

Examples (numbers)

The format string '#,0.00' or '#,##0.00' formats the number with group separators, and two digits after the decimal point.

PRINT FORMAT(1234567890.1234, '#,##0.00'); -- current language is en-US
PRINT FORMAT(1234567890.1234, '#,##0.00', 'en-US');
PRINT FORMAT(1234567890.1234, '#,##0.00', 'fr-FR');
PRINT FORMAT(1234567890.1234, '#,##0.00', 'fr-CH');

The result is:

1,234,567,890.12
1,234,567,890.12
1 234 567 890,12
1'234'567'890.12

Examples (datetime)

DECLARE @d datetime = '2000-07-01T04:05:06.123456789';

PRINT FORMAT(@d, 'f', 'de-DE');                -- standard format string
PRINT FORMAT(@d, 'yyyy/MM/dd HH:mm', 'de-DE'); -- custom format string

The result is:

Samstag, 1. Juli 2000 04:05
2000.07.01 04:05