FORMAT

FORMAT#

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Syntax#

FORMAT ( value, format [, culture ] )

Arguments#

value

Expression of a supported data type to format.

format

nvarchar format pattern.

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, “C” or “D”), or as a pattern of custom characters for dates and numeric values (for example, “MMMM DD, yyyy (dddd)”). Composite formatting is not supported. For a full explanation of these formatting patterns, consult the .NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats.

culture

Optional nvarchar argument specifying a culture.

If the culture argument is not provided, the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument. If the culture argument is not valid, FORMAT raises an error.

Return types#

nvarchar or null

The length of the return value is determined by the format.

Standard numeric format strings#

The following table describes the standard numeric format specifiers and displays sample output produced by each format specifier.

Format specifier

Name

Description with examples

“C” or “c”

Currency
A currency value.
123.456 (“C”, en-US) -> $123.46
123.456 (“C”, fr-FR) -> 123,46 €
123.456 (“C”, ja-JP) -> ¥123
-123.456 (“C3”, en-US) -> ($123.456)
-123.456 (“C3”, fr-FR) -> -123,456 €
-123.456 (“C3”, ja-JP) -> -¥123.456

“D” or “d”

Decimal
Integer digits with optional negative sign.
1234 (“D”) -> 1234
-1234 (“D6”) -> -001234

“E” or “e”

Exponential (scientific)
Exponential notation.
1052.0329112756 (“E”, en-US) -> 1.052033E+003
1052.0329112756 (“e”, fr-FR) -> 1,052033e+003
-1052.0329112756 (“e2”, en-US) -> -1.05e+003
-1052.0329112756 (“E2”, fr-FR) -> -1,05E+003

“F” or “f”

Fixed-point
Integral and decimal digits with optional negative sign.
1234.567 (“F”, en-US) -> 1234.57
1234.567 (“F”, de-DE) -> 1234,57
1234 (“F1”, en-US) -> 1234.0
1234 (“F1”, de-DE) -> 1234,0
-1234.56 (“F4”, en-US) -> -1234.5600
-1234.56 (“F4”, de-DE) -> -1234,5600

“G” or “g”

General

The more compact of either fixed-point or scientific notation.

-123.456 (“G”, en-US) -> -123.456
-123.456 (“G”, sv-SE) -> -123,456
123.4546 (“G4”, en-US) -> 123.5
123.4546 (“G4”, sv-SE) -> 123,5
-1.234567890e-25 (“G”, en-US) -> -1.23456789E-25
-1.234567890e-25 (“G”, sv-SE) -> -1,23456789E-25

“N” or “n”

Number

Integral and decimal digits, group separators, and a decimal separator with optional negative sign.

1234.567 (“N”, en-US) -> 1,234.57
1234.567 (“N”, ru-RU) -> 1 234,57
1234 (“N1”, en-US) -> 1,234.0
1234 (“N1”, ru-RU) -> 1 234,0
-1234.56 (“N3”, en-US) -> -1,234.560
-1234.56 (“N3”, ru-RU) -> -1 234,560

“P” or “p”

Percent

Number multiplied by 100 and displayed with a percent symbol.

1 (“P”, en-US) -> 100.00 %
1 (“P”, fr-FR) -> 100,00 %
-0.39678 (“P1”, en-US) -> -39.7 %
-0.39678 (“P1”, fr-FR) -> -39,7 %

“R” or “r”

Round-trip

A string that can round-trip to.

123456789.12345678 (“R”) -> 123456789.12345678
-1234567890.12345678 (“R”) -> -1234567890.1234567

“X” or “x”

Hexadecimal

A hexadecimal string.

255 (“X”) -> FF
-1 (“x”) -> ff
255 (“x4”) -> 00ff
-1 (“X4”) -> 00FF

Custom numeric format strings#

You can create a custom numeric format string, which consists of one or more custom numeric specifiers, to define how to format numeric data. A custom numeric format string is any format string that is not a standard numeric format string above.

Format specifier

Name

Description with examples

“0”

Zero placeholder

Replaces the zero with the corresponding digit if one is present; otherwise, zero appears in the result string. The “00” specifier causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used.

1234.5678 (“00000”) -> 01235
0.45678 (“0.00”, en-US) -> 0.46
0.45678 (“0.00”, fr-FR) -> 0,46
34.5 (“00”, en-US) -> value 35.

“#”

Digit placeholder

Replaces the “#” symbol with the corresponding digit if one is present; otherwise, no digit appears in the result string. Note that no digit appears in the result string if the corresponding digit in the input string is a non-significant 0. For example, 0003 (“####”) -> 3.

1234.5678 (“#####”) -> 1235
0.45678 (“#.##”, en-US) -> .46
0.45678 (“#.##”, fr-FR) -> ,46

“.”

Decimal point

Determines the location of the decimal separator in the result string.

0.45678 (“0.00”, en-US) -> 0.46
0.45678 (“0.00”, fr-FR) -> 0,46

“,”

Group separator and number scaling

Serves as both a group separator and a number scaling specifier. As a group separator, it inserts a localized group separator character between each group. As a number scaling specifier, it divides a number by 1000 for each comma specified.

Group separator specifier:
2147483647 (“##,#”, en-US) -> 2,147,483,647
2147483647 (“##,#”, es-ES) -> 2.147.483.647
Scaling specifier:
2147483647 (“#,#,,”, en-US) -> 2,147
2147483647 (“#,#,,”, es-ES) -> 2.147

“%”

Percentage placeholder

Multiplies a number by 100 and inserts a localized percentage symbol in the result string.

0.3697 (“%#0.00”, en-US) -> %36.97
0.3697 (“%#0.00”, el-GR) -> %36,97
0.3697 (“##.0 %”, en-US) -> 37.0 %
0.3697 (“##.0 %”, el-GR) -> 37,0 %

“‰”

Per mille placeholder

Multiplies a number by 1000 and inserts a localized per mille symbol in the result string.

0.03697 (“#0.00‰”, en-US) -> 36.97‰
0.03697 (“#0.00‰”, ru-RU) -> 36,97‰
“E0”
“E+0”
“E-0”
“e0”
“e+0”
“e-0”

Exponential notation

If followed by at least one 0 (zero), formats the result using exponential notation. The case of “E” or “e” indicates the case of the exponent symbol in the result string. The number of zeros following the “E” or “e” character determines the minimum number of digits in the exponent. A plus sign (+) indicates that a sign character always precedes the exponent. A minus sign (-) indicates that a sign character precedes only negative exponents.

987654 (“#0.0e0”) -> 98.8e4
1503.92311 (“0.0##e+00”) -> 1.504e+03
1.8901385E-16 (“0.0e+00”) -> 1.9e-16

“\”

Escape character

Causes the next character to be interpreted as a literal rather than as a custom format specifier.

987654 (”###00#”) -> #987654#

‘string’ “string”

Literal string delimiter

Indicates that the enclosed characters should be copied to the result string unchanged.

68 (”# ‘degrees’”) -> 68 degrees
68 (“#’ degrees’”) -> 68 degrees

;

Section separator

Defines sections with separate format strings for positive, negative, and zero numbers.

12.345 (“#0.0#;(#0.0#);-0-”) -> 12.35
0 (“#0.0#;(#0.0#);-0-”) -> -0-
-12.345 (“#0.0#;(#0.0#);-0-”) -> (12.35)
12.345 (“#0.0#;(#0.0#)”) -> 12.35
0 (“#0.0#;(#0.0#)”) -> 0.0
-12.345 (“#0.0#;(#0.0#)”) -> (12.35)

Other

All other characters

The character is copied to the result string unchanged.

68 (”# °”) -> 68 °

Standard date and time format strings#

A standard date and time format string uses a single character as the format specifier to define the text representation of a DateTime or a DateTimeOffset value. Any date and time format string that contains more than one character, including white space, is interpreted as a custom date and time format string.

A standard or custom format string can be used in two ways:

  • To define the string that results from a formatting operation.

  • To define the text representation of a date and time value that can be converted to a DateTime or DateTimeOffset value by a parsing operation.

Format specifier

Name

Examples

“d”

Short date pattern
2009-06-15T13:45:30 -> 6/15/2009 (en-US)
2009-06-15T13:45:30 -> 15/06/2009 (fr-FR)
2009-06-15T13:45:30 -> 2009/06/15 (ja-JP)

“d”

Long date pattern
2009-06-15T13:45:30 -> Monday, June 15, 2009 (en-US)
2009-06-15T13:45:30 -> 15 июня 2009 г. (ru-RU)
2009-06-15T13:45:30 -> Montag, 15. Juni 2009 (de-DE)

“f”

Full date/time pattern (short time)
2009-06-15T13:45:30 -> Monday, June 15, 2009 1:45 PM (en-US)
2009-06-15T13:45:30 -> den 15 juni 2009 13:45 (sv-SE)
2009-06-15T13:45:30 -> Δευτέρα, 15 Ιουνίου 2009 1:45 μμ (el-GR)

“F”

Full date/time pattern (long time)
2009-06-15T13:45:30 -> Monday, June 15, 2009 1:45:30 PM (en-US)
2009-06-15T13:45:30 -> den 15 juni 2009 13:45:30 (sv-SE)
2009-06-15T13:45:30 -> Δευτέρα, 15 Ιουνίου 2009 1:45:30 μμ (el-GR)

“g”

General date/time pattern (short time)
2009-06-15T13:45:30 -> 6/15/2009 1:45 PM (en-US)
2009-06-15T13:45:30 -> 15/06/2009 13:45 (es-ES)
2009-06-15T13:45:30 -> 2009/6/15 13:45 (zh-CN)

“G”

General date/time pattern (long time)
2009-06-15T13:45:30 -> 6/15/2009 1:45:30 PM (en-US)
2009-06-15T13:45:30 -> 15/06/2009 13:45:30 (es-ES)
2009-06-15T13:45:30 -> 2009/6/15 13:45:30 (zh-CN)

“M”, “m”

Month/day pattern
2009-06-15T13:45:30 -> June 15 (en-US)
2009-06-15T13:45:30 -> 15. juni (da-DK)
2009-06-15T13:45:30 -> 15 Juni (id-ID)

“O”, “o”

Round-trip date/time pattern
DateTime values:
2009-06-15T13:45:30 (DateTimeKind.Local) –> 2009-06-15T13:45:30.0000000-07:00
2009-06-15T13:45:30 (DateTimeKind.Utc) –> 2009-06-15T13:45:30.0000000Z
2009-06-15T13:45:30 (DateTimeKind.Unspecified) –> 2009-06-15T13:45:30.0000000
DateTimeOffset values:
2009-06-15T13:45:30-07:00 –> 2009-06-15T13:45:30.0000000-07:00

“R”, “r”

RFC1123 pattern
2009-06-15T13:45:30 -> Mon, 15 Jun 2009 20:45:30 GMT

“s”

Sortable date/time pattern
2009-06-15T13:45:30 (DateTimeKind.Local) -> 2009-06-15T13:45:30
2009-06-15T13:45:30 (DateTimeKind.Utc) -> 2009-06-15T13:45:30

“t”

Short time pattern
2009-06-15T13:45:30 -> 1:45 PM (en-US)
2009-06-15T13:45:30 -> 13:45 (hr-HR)
2009-06-15T13:45:30 -> 01:45 م (ar-EG)

“T”

Long time pattern
2009-06-15T13:45:30 -> 1:45:30 PM (en-US)
2009-06-15T13:45:30 -> 13:45:30 (hr-HR)
2009-06-15T13:45:30 -> 01:45:30 م (ar-EG)

“u”

Universal sortable date/time pattern
With a DateTime value:
2009-06-15T13:45:30 -> 2009-06-15 13:45:30Z

With a DateTimeOffset value:
2009-06-15T13:45:30 -> 2009-06-15 20:45:30Z

“U”

Universal full date/time pattern
2009-06-15T13:45:30 -> Monday, June 15, 2009 8:45:30 PM (en-US)
2009-06-15T13:45:30 -> den 15 juni 2009 20:45:30 (sv-SE)
2009-06-15T13:45:30 -> Δευτέρα, 15 Ιουνίου 2009 8:45:30 μμ (el-GR)

“Y”, “y”

Year month pattern
2009-06-15T13:45:30 -> June 2009 (en-US)
2009-06-15T13:45:30 -> juni 2009 (da-DK)
2009-06-15T13:45:30 -> Juni 2009 (id-ID)

Custom date and time format strings#

A date and time format string defines the text representation of a DateTime or DateTimeOffset value that results from a formatting operation. It can also define the representation of a date and time value that is required in a parsing operation in order to successfully convert the string to a date and time. A custom format string consists of one or more custom date and time format specifiers. Any string that is not a standard date and time format string is interpreted as a custom date and time format string.

Format specifier

Description with examples

“d”

The day of the month, from 1 through 31.

2009-06-01T13:45:30 -> 1
2009-06-15T13:45:30 -> 15

“dd”

The day of the month, from 01 through 31.

2009-06-01T13:45:30 -> 01
2009-06-15T13:45:30 -> 15

“ddd”

The abbreviated name of the day of the week.

2009-06-15T13:45:30 -> Mon (en-US)
2009-06-15T13:45:30 -> Пн (ru-RU)
2009-06-15T13:45:30 -> lun. (fr-FR)

“dddd”

The full name of the day of the week.

2009-06-15T13:45:30 -> Monday (en-US)
2009-06-15T13:45:30 -> понедельник (ru-RU)
2009-06-15T13:45:30 -> lundi (fr-FR)

“f”

The tenths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 6
2009-06-15T13:45:30.05 -> 0

“ff”

The hundredths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 61
2009-06-15T13:45:30.0050000 -> 00

“fff”

The milliseconds in a date and time value.

6/15/2009 13:45:30.617 -> 617
6/15/2009 13:45:30.0005 -> 000

“ffff”

The ten thousandths of a second in a date and time value.

2009-06-15T13:45:30.6175000 -> 6175
2009-06-15T13:45:30.0000500 -> 0000

“fffff”

The hundred thousandths of a second in a date and time value.

2009-06-15T13:45:30.6175400 -> 61754
6/15/2009 13:45:30.000005 -> 00000

“ffffff”

The millionths of a second in a date and time value.

2009-06-15T13:45:30.6175420 -> 617542
2009-06-15T13:45:30.0000005 -> 000000

“fffffff”

The ten millionths of a second in a date and time value.

2009-06-15T13:45:30.6175425 -> 6175425
2009-06-15T13:45:30.0001150 -> 0001150

“F”

If non-zero, the tenths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 6
2009-06-15T13:45:30.0500000 -> (no output)

“FF”

If non-zero, the hundredths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 61
2009-06-15T13:45:30.0050000 -> (no output)

“FFF”

If non-zero, the milliseconds in a date and time value.

2009-06-15T13:45:30.6170000 -> 617
2009-06-15T13:45:30.0005000 -> (no output)

“FFFF”

If non-zero, the ten thousandths of a second in a date and time value.

2009-06-15T13:45:30.5275000 -> 5275
2009-06-15T13:45:30.0000500 -> (no output)

“FFFFF”

If non-zero, the hundred thousandths of a second in a date and time value.

2009-06-15T13:45:30.6175400 -> 61754
2009-06-15T13:45:30.0000050 -> (no output)

“FFFFFF”

If non-zero, the millionths of a second in a date and time value.

2009-06-15T13:45:30.6175420 -> 617542
2009-06-15T13:45:30.0000005 -> (no output)

“FFFFFFF”

If non-zero, the ten millionths of a second in a date and time value.

2009-06-15T13:45:30.6175425 -> 6175425
2009-06-15T13:45:30.0001150 -> 000115

“g”, “gg”

The period or era.

2009-06-15T13:45:30.6170000 -> A.D.

“h”

The hour, using a 12-hour clock from 1 to 12.

2009-06-15T01:45:30 -> 1
2009-06-15T13:45:30 -> 1

“hh”

The hour, using a 12-hour clock from 01 to 12.

2009-06-15T01:45:30 -> 01
2009-06-15T13:45:30 -> 01

“H”

The hour, using a 24-hour clock from 0 to 23.

2009-06-15T01:45:30 -> 1
2009-06-15T13:45:30 -> 13

“HH”

The hour, using a 24-hour clock from 00 to 23.

2009-06-15T01:45:30 -> 01
2009-06-15T13:45:30 -> 13

“K”

Time zone information.

With DateTime values:
2009-06-15T13:45:30,
Kind Unspecified -> 2009-06-15T13:45:30,
Kind Utc -> Z 2009-06-15T13:45:30,
Kind Local -> -07:00 (depends on local computer settings)
With DateTimeOffset values:
2009-06-15T01:45:30-07:00 –> -07:00
2009-06-15T08:45:30+00:00 –> +00:00

“m”

The minute, from 0 through 59.

2009-06-15T01:09:30 -> 9
2009-06-15T13:29:30 -> 29

“mm”

The minute, from 00 through 59.

2009-06-15T01:09:30 -> 09
2009-06-15T01:45:30 -> 45

“M”

The month, from 1 through 12.

2009-06-15T13:45:30 -> 6

“MM”

The month, from 01 through 12.

2009-06-15T13:45:30 -> 06

“MMM”

The abbreviated name of the month.

2009-06-15T13:45:30 -> Jun (en-US)
2009-06-15T13:45:30 -> juin (fr-FR)
2009-06-15T13:45:30 -> Jun (zu-ZA)

“MMMM”

The full name of the month.

2009-06-15T13:45:30 -> June (en-US)
2009-06-15T13:45:30 -> juni (da-DK)
2009-06-15T13:45:30 -> uJuni (zu-ZA)

“s”

The second, from 0 through 59.

2009-06-15T13:45:09 -> 9

“ss”

The second, from 00 through 59.

2009-06-15T13:45:09 -> 09

“t”

The first character of the AM/PM designator.

2009-06-15T13:45:30 -> P (en-US)
2009-06-15T13:45:30 -> 午 (ja-JP)
2009-06-15T13:45:30 -> (fr-FR)

“tt”

The AM/PM designator.

2009-06-15T13:45:30 -> PM (en-US)
2009-06-15T13:45:30 -> 午後 (ja-JP)
2009-06-15T13:45:30 -> (fr-FR)

“y”

The year, from 0 to 99.

0001-01-01T00:00:00 -> 1
0900-01-01T00:00:00 -> 0
1900-01-01T00:00:00 -> 0
2009-06-15T13:45:30 -> 9
2019-06-15T13:45:30 -> 19

“yy”

The year, from 00 to 99.

0001-01-01T00:00:00 -> 01
0900-01-01T00:00:00 -> 00
1900-01-01T00:00:00 -> 00
2019-06-15T13:45:30 -> 19

“yyy”

The year, with a minimum of three digits.

0001-01-01T00:00:00 -> 001
0900-01-01T00:00:00 -> 900
1900-01-01T00:00:00 -> 1900
2009-06-15T13:45:30 -> 2009

“yyyy”

The year as a four-digit number.

0001-01-01T00:00:00 -> 0001
0900-01-01T00:00:00 -> 0900
1900-01-01T00:00:00 -> 1900
2009-06-15T13:45:30 -> 2009

“yyyyy”

The year as a five-digit number.

0001-01-01T00:00:00 -> 00001
2009-06-15T13:45:30 -> 02009

“z”

Hours offset from UTC, with no leading zeros.

2009-06-15T13:45:30-07:00 -> -7

“zz”

Hours offset from UTC, with a leading zero for a single-digit value.

2009-06-15T13:45:30-07:00 -> -07

“zzz”

Hours and minutes offset from UTC.

2009-06-15T13:45:30-07:00 -> -07:00

“:”

The time separator.

2009-06-15T13:45:30 -> : (en-US)
2009-06-15T13:45:30 -> . (it-IT)
2009-06-15T13:45:30 -> : (ja-JP)

“/”

The date separator.

2009-06-15T13:45:30 -> / (en-US)
2009-06-15T13:45:30 -> - (ar-DZ)
2009-06-15T13:45:30 -> . (tr-TR)

“string” ‘string’

Literal string delimiter.

2009-06-15T13:45:30 (“arr:” h:m t) -> arr: 1:45 P
2009-06-15T13:45:30 (‘arr:’ h:m t) -> arr: 1:45 P

%

Defines the following character as a custom format specifier.

2009-06-15T13:45:30 (%h) -> 1

\

The escape character.

2009-06-15T13:45:30 (h \h) -> 1 h

Any other character

The character is copied to the result string unchanged.

2009-06-15T01:45:30 (arr hh:mm t) -> arr 01:45 A

Examples#

A. Simple FORMAT example#

The following example returns a simple date formatted for different cultures.

DECLARE @d DATE = '11/22/2020';
SELECT FORMAT( @d, 'd', 'en-US' ) 'US English'
     , FORMAT( @d, 'd', 'en-gb' ) 'Great Britain English'
     , FORMAT( @d, 'd', 'de-de' ) 'German'
     , FORMAT( @d, 'd', 'zh-cn' ) 'Simplified Chinese (PRC)';

SELECT FORMAT( @d, 'D', 'en-US' ) 'US English'
     , FORMAT( @d, 'D', 'en-gb' ) 'Great Britain English'
     , FORMAT( @d, 'D', 'de-de' ) 'German'
     , FORMAT( @d, 'D', 'zh-cn' ) 'Chinese (Simplified PRC)';

The result set:

US English  Great Britain English  German      Simplified Chinese (PRC)
----------  ---------------------  ----------  ------------------------
11/22/2020  22/11/2020             22.11.2020  2020/11/22

US English                   Great Britain English   German                      Chinese (Simplified PRC)
---------------------------  ----------------------  --------------------------  ------------------------
Sunday, November 22, 2020    22 November 2020        Sonntag, 22. November 2020  2020年11月22日

B. FORMAT with custom formatting strings#

The following example shows formatting numeric values by specifying a custom format. The example assumes that the current date is September 27, 2012. For more information about these and other custom formats, see Custom Numeric Format Strings.

DECLARE @d DATE = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date'
     , FORMAT(123456789,'###-##-####')     AS 'Custom Number';

The result set:

Date        Custom Number
----------  -------------
22/11/2020  123-45-6789

C. FORMAT with numeric types#

In this example, the column is returned unformatted and then formatted by specifying the .NET Number format, General format, and Currency format types. For more information about these and other numeric formats, see Standard Numeric Format Strings.

SELECT EndOfDayRate
     , FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Number Format'
     , FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format'
     , FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'
 FROM (SELECT 1.0002 AS EndOfDayRate UNION ALL
       SELECT 1.55   UNION ALL
       SELECT 1.9419 UNION ALL
       SELECT 1.4683 UNION ALL
       SELECT 8.2784
     ) AS data;

CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format
-------------- ------------  --------------  --------------  ---------------
1              1.0002        1.00            1.0002          $1.00
2              1.55          1.55            1.5500          $1.55
3              1.9419        1.94            1.9419          $1.94
4              1.4683        1.47            1.4683          $1.47
5              8.2784        8.28            8.2784          $8.28

This example specifies the German culture (de-de).

SELECT EndOfDayRate
     , FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Number Format'
     , FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format'
     , FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'
 FROM (SELECT 1.0002 AS EndOfDayRate UNION ALL
       SELECT 1.55   UNION ALL
       SELECT 1.9419 UNION ALL
       SELECT 1.4683 UNION ALL
       SELECT 8.2784
     ) AS data;

CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format
-------------- ------------  --------------  --------------  ---------------
1              1.0002        1,00            1,0002          1,00 €
2              1.55          1,55            1,5500          1,55 €
3              1.9419        1,94            1,9419          1,94 €
4              1.4683        1,47            1,4683          1,47 €
5              8.2784        8,28            8,2784          8,28 €

D. FORMAT with time data types#

FORMAT returns NULL in these cases because . and : are not escaped with backslash ‘\’.

SELECT FORMAT(cast('07:35' as time), N'hh.mm');   --> returns NULL
SELECT FORMAT(cast('07:35' as time), N'hh:mm');   --> returns NULL

Format returns a formatted string because the . and : are escaped.

SELECT FORMAT(cast('07:35' as time), N'hh\.mm');  --> returns 07.35
SELECT FORMAT(cast('07:35' as time), N'hh\:mm');  --> returns 07:35

Format returns a formatted current time with AM or PM specified.

SELECT FORMAT(cast('1900-01-01 15:46' as datetime), N'hh:mm tt'); --> returns 03:46 PM
SELECT FORMAT(cast('1900-01-01 15:46' as datetime), N'hh:mm t');  --> returns 03:46 P

Format returns the specified time, displaying AM.

SELECT FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm tt') --> returns 01:00 AM
SELECT FORMAT(CAST('2018-01-01 01:00' AS datetime2), N'hh:mm t')  --> returns 01:00 A

Format returns the specified time, displaying PM.

SELECT FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm tt') --> returns 02:00 PM
SELECT FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'hh:mm t')  --> returns 02:00 P

Format returns the specified time in 24h format.

SELECT FORMAT(CAST('2018-01-01 14:00' AS datetime2), N'HH:mm')    --> returns 14:00

See Also#