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.


FORMAT ( numeric_expression , format_string [, language] )

FORMAT ( datetime_expression , format_string [, language] )


is a numeric expression to format. It is of type int, bigint, money, numeric, float.
is a datetime expression to format. It is of type date, time, datetime.
is the format pattern, of type varchar.
See Numeric Format Strings and Datetime Format Strings for description of format string.
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

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
RSQL, a simple alternative to Microsoft SQL Server