Click here to Skip to main content
11,432,427 members (60,290 online)
Click here to Skip to main content

SQL Server's FORMAT() function

, 6 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
SQL Server's FORMAT function - definition, syntax and different formatting options with samples.

Table Of Contents



Introduction

Developers often use formatting options while working with date time , numbers etc. We all know formatting should be done at the presentation layer but still some times we use CAST, CONVERT or STR for some of our formatting requirements in stored procedure or sql queries. From SQL Server 2012, FORMAT() funcation was added to make formatting easier. In this article I will take you through FORMAT() function with the help of formatting samples.

FORMAT()

FORMAT() is a new built-in SQL Server function that is available from SQL Server 2012 onwards. This string function returns the value formatted in the specified format using the optional culture parameter value. This function relies on the presence of .the .NET Framework Common Language Runtime (CLR).

Use the FORMAT function for locale-aware formatting of date/time and number values as strings.

FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.

Note: FORMAT() is not an Sql Server native function instead it is .NET CLR dependent function.This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Syntax:-

FORMAT(<value>, <format_string> [, culture ] ) 

Where value is an expression of a supported data type to format. The format argument must contain a valid .NET Framework format string, either as a standard format string or as a pattern of custom characters for dates and numeric values. Composite formatting is not supported. format argument is in an nvarchar format pattern.Optional nvarchar argument specifying a culture. If the culture argument is not provided, the language of the current session is used. If the culture argument is not valid, FORMAT raises an error.

Sample:-

DECLARE @d DATETIME = '10/01/2011';

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

--US English Result    Great Britain English Result    German Result    Chinese (Simplified PRC) Result
--Saturday, October 01, 2011    01 October 2011    Samstag, 1. Oktober 2011    2011年10月1日

Let us explore FORMAT function with different formatting options

Standard Numeric Format Strings

Standard numeric format strings are used to format common numeric types. A standard numeric format string takes the form Axx, where A is a single alphabetic character called the format specifier, and xx is an optional integer called the precision specifier. 

Note: Any numeric format string that contains more than one alphabetic character, including white space, is interpreted as a custom numeric format string. The precision specifier controls the number of digits in the string representation of a number. It does not round the number itself. 

Format Specifier Description and Examples
C or c

Currency.

The currency format specifier converts a number to a string that represents a currency amount. The conversion is controlled by the optional cutlture parameter,  If the culture argument is not provided, the language of the current session is used. The precision specifier indicates the desired number of decimal places. If omitted, the default currency precision is controlled by the culture or language of the current session. 

If the value to be formatted has more than the specified or default number of decimal places, the fractional value is rounded in the result string.

This format is supported by all numeric types.

DECLARE @d float = 12345.6789

SELECT FORMAT(@d, 'C')  --$12,345.68
SELECT FORMAT(@d, 'C5') --$12,345.67890
SELECT FORMAT(@d, 'C', 'en-US') --$12,345.68
SELECT FORMAT(@d, 'C', 'fr-FR') --12 345,68 &euro;
SELECT FORMAT(@d, 'C', 'ja-JP'); --&yen;12,346
D or d

Decimal.

The decimal format specifier converts a number to a string of decimal digits (0-9), prefixed by a minus sign if the number is negative. The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier. If no precision specifier is specified, the default is the minimum value required to represent the integer without leading zeros.

This format is supported only for integral types.

DECLARE @d INT = 123

SELECT FORMAT(@d, 'D')  -- 123
SELECT FORMAT(@d, 'D5') -- 00123
SELECT FORMAT(-234, 'D', 'fr-FR') -- -234
E or e

Exponential.

The exponential format specifier converts a number to a string of the form "-d.ddd…E+ddd" or "-d.ddd…e+ddd", where each "d" indicates a digit (0-9). One digit always precedes the decimal point, a minimum of three digits follow the ± sign, and the case determines the prefix of the exponent (E or an e). If the precision specifier is omitted, a default of E6 is used.

This format is supported by all numeric types.

DECLARE @d FLOAT = 12345.6789

SELECT FORMAT(@d, 'E')  -- 1.234568E+004
SELECT FORMAT(@d, 'E10') -- 1.2345678900E+004
SELECT FORMAT(-234, 'e4') -- -2.3400e+002
F or f

Fixed-Point.

The fixed-point format specifier converts a number to a string of the form "-ddd.ddd…" where each "d" indicates a digit (0-9). The string starts with a minus sign if the number is negative. If the precision specifier is omitted, the default numeric precision given by the Language property is used.

This format is supported by all numeric types.

DECLARE @d FLOAT = 18934.1879

SELECT FORMAT(@d, 'F')  -- 18934.19
SELECT FORMAT(@d, 'F1') -- 18934.2
SELECT FORMAT(1234, 'F5') -- 1234.00000
SELECT FORMAT(-234, 'F0') -- -234
G or g

General.

The general format specifier converts a number to the most compact of either fixed-point or scientific notation, depending on the type of the number and whether a precision specifier is present. If the precision specifier is omitted or zero, the type of the number determines the default precision.

This format is supported by all numeric types.

DECLARE @d FLOAT 

SET @d=12345.6789

SELECT FORMAT(@d, 'G')  -- 12345.6789
SELECT FORMAT(@d, 'G', 'fr-FR') -- 12345,6789
SELECT FORMAT(@d, 'G2')  -- 1.2E+04

SET @d=.0023
SELECT FORMAT(@d, 'G') -- 0.0023 
N or n

Numeric.

The numeric format specifier converts a number to a string of the form "-d,ddd,ddd.ddd…", where "-" indicates a negative number symbol if required, "d" indicates a digit (0-9), "," indicates a group separator, and "." indicates a decimal point symbol. The precision specifier indicates the desired number of digits after the decimal point. If the precision specifier is omitted, the number of decimal places is defined by the current culture or language property.

This format is supported by all numeric types.

DECLARE @d FLOAT 

SET @d=12345.6789

SELECT FORMAT(@d, 'N')  -- 12,345.68
SELECT FORMAT(@d, 'N1', 'sv-SE') -- 12 345,7

SET @d=123456789
SELECT FORMAT(@d, 'N1')  -- 123,456,789.0
P or p

Percent.

The percent format specifier multiplies a number by 100 and converts it to a string that represents a percentage. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision is guided by the current culture or language property.

This format is supported by all numeric types.

DECLARE @d FLOAT =.2468013

SELECT FORMAT(@d, 'P')  -- 24.68 %
SELECT FORMAT(@d, 'P', 'hr-HR') -- 24,68%
R or r

Round-trip.

The round-trip ("R") format specifier guarantees that a numeric value that is converted to a string will be parsed back into the same numeric value. Precision specifier is ignored in this format. 

This format is supported only for the real, float, and bigint types.

DECLARE @d FLOAT = 1.623e-21

SELECT FORMAT(@d, 'R')  -- 1.623E-21
SELECT FORMAT(@d, 'r', 'fr-FR') -- 1,623E-21

SET @d=3.1415926535897931
SELECT FORMAT(@d, 'R')  -- 3.1415926535897931
X or x

Hexadecimal.

The hexadecimal format specifier converts a number to a string of hexadecimal digits. The case of the format specifier indicates whether to use uppercase or lowercase characters for hexadecimal digits that are greater than 9. 

X produces uppercase (ABCDEF) for digits greater than 9; x produces lowercase (abcdef). Decimal number 123 is correspondingly converted to hexadecimal 7b.

The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier.

This format is supported only for integer types.

DECLARE @d INT = 123456789

SELECT FORMAT(@d, 'X')  -- 75BCD15
SELECT FORMAT(@d, 'x') -- 75bcd15
SELECT FORMAT(@d, 'X12') -- 0000075BCD15
Any other single character

Unknown specifier. Returns NULL.

DECLARE @d INT = 123456789

SELECT FORMAT(@d, 'S')  -- NULL

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.

Format Specifier Description and Examples
0

Zero-placeholder.

The "0" custom format specifier serves as a zero-placeholder symbol. If the value that is being formatted has a digit in the position where the zero appears in the format string, that digit is copied to the result string; otherwise, a zero appears in the result string. The position of the leftmost zero before the decimal point and the rightmost zero after the decimal point determines the range of digits that are always present 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. For example, formatting 34.5 with "00" would result in the value 35.

DECLARE @d FLOAT = 34.5

SELECT FORMAT(@d, '0.0')  -- 34.5
SELECT FORMAT(@d, '00')  -- 35
SELECT FORMAT(@d, '0000') -- 0035
SELECT FORMAT(@d, '0000.00') -- 0034.50
#

Digit-placeholder.

The "#" custom format specifier serves as a digit-placeholder symbol. If the value that is being formatted has a digit in the position where the "#" symbol appears in the format string, that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string. 

This specifier never displays 0 character if it is not a significant digit, even if 0 is the only digit in the string.

The "##" format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used. For example, formatting 34.5 with "##" would result in the value 35.

DECLARE @d FLOAT = 012.30

SELECT FORMAT(@d, '###.##')  -- 12.3
SET @d = 1234567890
SELECT FORMAT(@d, '(###)###-####')  -- (123)456-7890 
.

Decimal Point.

The "." custom format specifier inserts a localized decimal separator into the result string. The first period in the format string determines the location of the decimal separator in the formatted value; any additional periods are ignored.

The actual character used as the decimal separator is determined by the Language property. For example, the French decimal point is actually a comma (,).

DECLARE @d FLOAT = 012.30

SELECT FORMAT(@d, '###.##', 'fr-FR')  -- 12,3
SELECT FORMAT(@d, '###.##')  -- 12.3
,

Thousand separator and number scaling.

The "," character serves as both a group separator and a number scaling specifier. You can use group separator and number scaling specifiers in the same format string.

  • Group separator: If one or more commas are specified between two digit placeholders (0 or #) that format the integral digits of a number, a group separator character is inserted between each number group in the integral part of the output.
  • Number scaling specifier: If one or more commas are specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 for each comma. For example, if the string "0,," is used to format the number 100 million, the output is "100".

The actual character used as the decimal separator is determined by the Language property.

DECLARE @d FLOAT = 1234567890

SELECT FORMAT(@d, '#,#')  -- 1,234,567,890
SELECT FORMAT(@d, '#,##0,,')  -- 1,235
%

Percentage placeholder.

A percent sign (%) in a format string causes a number to be multiplied by 100 before it is formatted. The localized percent symbol is inserted in the number at the location where the % appears in the format string. 

The percent character used is dependent on the current Language.

DECLARE @d FLOAT = .086

SELECT FORMAT(@d, '#0.##%')  -- 8.6%

E0 or

E+0 or

E-0 or

e0' or

e+0 or

e-0

Scientific notation.

Format using scientific notation. The number of 0s determines the minimum number of digits to output for the exponent. The E+ and e+ formats indicate that a sign character (plus or minus) should always precede the exponent. The E, E-, e, or e- formats indicate that a sign character should only precede negative exponents.

DECLARE @d FLOAT = 86000

SELECT FORMAT(@d, '0.###E+0')  -- 8.6E+4
SELECT FORMAT(@d, '0.###E+000')  -- 8.6E+004
SELECT FORMAT(@d, '0.###E-000')  -- 8.6E004
\

Escape character.

To prevent a character from being interpreted as a format specifier, you can precede it with a backslash, which is the escape character. The escape character signifies that the following character is a character literal that should be included in the result string unchanged.

To include a backslash in a result string, you must escape it with another backslash (\\).

DECLARE @d FLOAT = 86000

SELECT FORMAT(@d, 'C')  -- $86,000.00
SELECT FORMAT(@d, '0\C')  -- 86000C
'string', "string"

Literal string.

Characters enclosed in single or double quotes are copied to the result string literally, and do not affect formatting.

DECLARE @d FLOAT = 68    

SELECT FORMAT(@d, '# "degrees"')  -- 68 degrees
;

Section separator.

The semicolon (;) is a conditional format specifier that applies different formatting to a number depending on whether its value is positive, negative, or zero. To produce this behavior, a custom format string can contain up to three sections separated by semicolons.

  • One section:The format string applies to all values.
  • Two sections:The first section applies to positive values and zeros, and the second section applies to negative values. If the number to be formatted is negative, but becomes zero after rounding according to the format in the second section, the resulting zero is formatted according to the first section.
  • Three sections:The first section applies to positive values, the second section applies to negative values, and the third section applies to zeros. The second section can be left empty (by having nothing between the semicolons), in which case the first section applies to all nonzero values. If the number to be formatted is nonzero, but becomes zero after rounding according to the format in the first or second section, the resulting zero is formatted according to the third section

Section separators ignore any preexisting formatting associated with a number when the final value is formatted.

DECLARE @posValue FLOAT = 1234;
DECLARE @negValue FLOAT = -1234;
DECLARE @zeroValue FLOAT = 0;    

SELECT FORMAT(@posValue, '##;(##)')  -- 1234
SELECT FORMAT(@negValue, '##;(##)')  -- (1234)
SELECT FORMAT(@zeroValue, '##;(##);**Zero**')  -- **Zero**
Other

All other characters.

The character is copied to the result string unchanged.

DECLARE @d FLOAT = 68    

SELECT FORMAT(@d, '# &deg;')  -- 68 &deg;

Standard Date and Time Format Strings

A standard date and time format string uses a single format specifier to define the text representation of a date and time value. Below given table shows all standard date and time format strings, if you use any single character as format string then it will consider it as a single format specifier (standard), if it's an invalid single format specifier then NULL will be retuned.

In a formatting operation, a standard format string is simply an alias for a custom format string. The advantage of using an alias to refer to a custom format string is that, although the alias remains invariant, the custom format string itself can vary. This is important because the string representations of date and time values typically vary by culture. For example, the "d" standard format string indicates that a date and time value is to be displayed using a short date pattern. For the invariant culture, this pattern is "MM/dd/yyyy". For the fr-FR culture, it is "dd/MM/yyyy". For the ja-JP culture, it is "yyyy/MM/dd".

Note: A single format specifier used as standard date and time format string to define the text representation of a date and time 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;

Single Format Specifier Description andExamples
d

Short date pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'd') --4/28/2013
SELECT FORMAT(@d, 'd', 'en-US') --4/28/2013
SELECT FORMAT(@d, 'd', 'fr-FR') --28/04/2013
SELECT FORMAT(@d, 'd', 'ja-JP'); --2013/04/28 
D

Long date pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'D') --Sunday, April 28, 2013
SELECT FORMAT(@d, 'D', 'sv-SE') --den 28 april 2013
SELECT FORMAT(@d, 'D', 'fr-FR') --dimanche 28 avril 2013
SELECT FORMAT(@d, 'D', 'ja-JP'); --2013年4月28日
f

Full date/time pattern (short time).

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'f') --Sunday, April 28, 2013 4:54 PM
SELECT FORMAT(@d, 'f', 'de-DE') --Sonntag, 28. April 2013 16:54
SELECT FORMAT(@d, 'f', 'fr-FR') --dimanche 28 avril 2013 16:54
SELECT FORMAT(@d, 'f', 'sv-SE'); --den 28 april 2013 16:54 
g

Full date/time pattern (long time).

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'g') --4/28/2013 4:54 PM
SELECT FORMAT(@d, 'g', 'de-DE') --28.04.2013 16:54
SELECT FORMAT(@d, 'g', 'fr-FR') --28/04/2013 16:54
SELECT FORMAT(@d, 'g', 'sv-SE'); --2013-04-28 16:54 
G

General date/time pattern (short time).

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'G') --4/28/2013 4:54:08 PM
SELECT FORMAT(@d, 'G', 'hr-HR') --28.4.2013. 16:54:08
SELECT FORMAT(@d, 'G', 'id-ID') --28/04/2013 16:54:08
SELECT FORMAT(@d, 'G', 'el-GR'); --28/4/2013 4:54:08 &mu;&mu; 
M or m

Month/day pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'M') --April 28
SELECT FORMAT(@d, 'M', 'hr-HR') --28. travnja
SELECT FORMAT(@d, 'M', 'id-ID') --28 April
SELECT FORMAT(@d, 'M', 'el-GR'); --28 &Alpha;&pi;&rho;&iota;&lambda;ί&omicron;&upsilon;
SELECT FORMAT(@d, 'm') --April 28
SELECT FORMAT(@d, 'm', 'hr-HR') --28. travnja
SELECT FORMAT(@d, 'm', 'id-ID') --28 April
SELECT FORMAT(@d, 'm', 'el-GR'); --28 &Alpha;&pi;&rho;&iota;&lambda;ί&omicron;&upsilon;
O or o

Round-trip date/time pattern. The pattern for this specifier reflects a defined standard (ISO 8601). Therefore, it is always the same regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'O') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'O', 'hr-HR') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'O', 'id-ID') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'O', 'el-GR'); --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o', 'hr-HR') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o', 'id-ID') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o', 'el-GR'); --2013-04-28T16:54:08.0000000 
R or r

RFC1123 pattern.The output will be same regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'R') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'R', 'hr-HR') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'R', 'id-ID') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'R', 'el-GR'); --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r', 'hr-HR') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r', 'id-ID') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r', 'el-GR'); --Sun, 28 Apr 2013 16:54:08 GMT 
s

Sortable date/time pattern. The pattern reflects a defined standard (ISO 8601), and the property is read-only. Therefore, it is always the same, regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 's') --2013-04-28T16:54:08
SELECT FORMAT(@d, 's', 'hr-HR') --2013-04-28T16:54:08
SELECT FORMAT(@d, 's', 'id-ID') --2013-04-28T16:54:08
SELECT FORMAT(@d, 's', 'el-GR'); --2013-04-28T16:54:08 
t

Short time pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 't') --4:54 PM
SELECT FORMAT(@d, 't', 'hr-HR') --16:54
SELECT FORMAT(@d, 't', 'ar-EG') --04:54 م
SELECT FORMAT(@d, 't', 'el-GR'); --4:54 &mu;&mu;
T

Long time pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'T') --4:54:08 PM
SELECT FORMAT(@d, 'T', 'hr-HR') --16:54:08
SELECT FORMAT(@d, 'T', 'ar-EG') --04:54:08 م
SELECT FORMAT(@d, 'T', 'el-GR'); --4:54:08 &mu;&mu; 
u

Universal sortable date/time pattern. The output will be same regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'u') --2013-04-28 16:54:08Z
SELECT FORMAT(@d, 'u', 'hr-HR') --2013-04-28 16:54:08Z
SELECT FORMAT(@d, 'u', 'ar-EG') --2013-04-28 16:54:08Z
SELECT FORMAT(@d, 'u', 'el-GR'); --2013-04-28 16:54:08Z 
U

Universal full date/time pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'U') --Sunday, April 28, 2013 11:24:08 AM
SELECT FORMAT(@d, 'U', 'hr-HR') --28. travnja 2013. 11:24:08
SELECT FORMAT(@d, 'U', 'SV-se') --den 28 april 2013 11:24:08
SELECT FORMAT(@d, 'U', 'el-GR'); --&Kappa;&upsilon;&rho;&iota;&alpha;&kappa;ή, 28 &Alpha;&pi;&rho;&iota;&lambda;ί&omicron;&upsilon; 2013 11:24:08 &pi;&mu;
Y or y

Year month pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'Y') --April, 2013
SELECT FORMAT(@d, 'Y', 'hr-HR') --travanj, 2013
SELECT FORMAT(@d, 'Y', 'id-ID') --April, 2013
SELECT FORMAT(@d, 'Y', 'el-GR'); --&Alpha;&pi;&rho;ί&lambda;&iota;&omicron;&sigmaf; 2013
SELECT FORMAT(@d, 'y') --April, 2013
SELECT FORMAT(@d, 'y', 'hr-HR') --travanj, 2013
SELECT FORMAT(@d, 'y', 'id-ID') --April, 2013
SELECT FORMAT(@d, 'y', 'el-GR'); --&Alpha;&pi;&rho;ί&lambda;&iota;&omicron;&sigmaf; 2013
Any other single character

Unknown specifier. Returns NULL.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'Q') --NULL 
 

Remarks: The standard format string (Single Format Specifier) serves as a convenient abbreviation for a longer custom format string that is invariant. Four standard format strings fall into this category: "O" (or "o"), "R" (or "r"), "s", and "u". These strings correspond to custom format strings defined by the invariant culture. They produce string representations of date and time values that are intended to be identical across cultures.

Single Format Specifier Equivalent Custom Format String
O or o 'yyyy-MM-ddTHH:mm:ss.fffffff'
R or r 'ddd, dd MMM yyyy HH:mm:ss "GMT"'
s 'yyyy-MM-ddTHH:mm:ss'
u 'yyyy-MM-dd HH:mm:ss"Z"'



All single format specifier is easy to use if incase we want more customized format then we can go for cutom date and time format string.

Custom Date and Time Format Strings

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.
 

Custom Format Specifier Description and Examples
  Note : To use any of the custom date and time format specifiers as the only specifier in a format string (that is, to use the "d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" custom format specifier by itself), include a space before or after the specifier, or include a percent ("%") format specifier before the single custom date and time specifier.
--Day Format
d

The day of the month, from 1 through 31.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'd ')--9  
SELECT FORMAT(@d, 'd-M-yyyy')--9-4-2013
SELECT FORMAT(@d, 'd MMMM')--9 April
SELECT FORMAT(@d, 'd MMMM', 'fr-FR')--9 avril 
dd

The day of the month, from 01 through 31.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'dd')--09   
SELECT FORMAT(@d, 'dd-M-yyyy')--09-4-2013
SELECT FORMAT(@d, 'dd MMMM')--09 April
SELECT FORMAT(@d, 'dd MMMM', 'fr-FR')--09 avril 
ddd

The abbreviated name of the day of the week.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'ddd d')--Tue 9  
SELECT FORMAT(@d, 'ddd dd-M-yyyy')--Tue 09-4-2013
SELECT FORMAT(@d, 'ddd dd MMMM')--Tue 09 April
SELECT FORMAT(@d, 'ddd dd MMMM', 'fr-FR')--mar. 09 avril 
dddd

The full name of the day of the week.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'dddd d')--Tuesday 9  
SELECT FORMAT(@d, 'dddd dd-M-yyyy')--Tuesday 09-4-2013
SELECT FORMAT(@d, 'dddd dd MMMM')--Tuesday 09 April
SELECT FORMAT(@d, 'dddd dd MMMM', 'fr-FR')--mardi 09 avril 
--Month Format
M

The month, from 1 through 12.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'M ')--4   
SELECT FORMAT(@d, 'dd-M-yyyy', 'fr-FR')--09-4-2013 
MM

The month, from 01 through 12.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'MM')--04  
SELECT FORMAT(@d, 'dd-MM-yyyy', 'fr-FR')--09-04-2013 
MMM

The abbreviated name of the month.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'MMM')--Apr 
SELECT FORMAT(@d, 'MMM', 'ml-IN')--ഏപ്റില്
SELECT FORMAT(@d, 'MMM', 'fr-FR')--avr. 
MMMM

The full name of the month.

DECLARE @d DATETIME
SET @d='4/9/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'MMMM')--April 
SELECT FORMAT(@d, 'MMMM', 'ml-IN')--ഏപ്റില്
SELECT FORMAT(@d, 'MMMM', 'fr-FR')--avril 
--Year Format
y

The year, from 0 to 99.

DECLARE @d DATETIME
SET @d='4/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'y ')--1   
SELECT FORMAT(@d, 'd-M-y')--9-4-1
SELECT FORMAT(@d, 'd MMMM y')--9 April 1
SELECT FORMAT(@d, 'd MMMM y', 'fr-FR')--9 avril 1 
yy

The year, from 00 to 99.

DECLARE @d DATETIME
SET @d='4/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'yy')--01
SELECT FORMAT(@d, 'd-M-yy')--9-4-01
SELECT FORMAT(@d, 'd MMMM yy')--9 April 01
SELECT FORMAT(@d, 'd MMMM yy', 'fr-FR')--9 avril 01 
yyy

The year, with a minimum of three digits.

DECLARE @d DATETIME
SET @d='4/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'yyy')--2001
SELECT FORMAT(@d, 'd-M-yyy')--9-4-2001
SELECT FORMAT(@d, 'd MMMM yyy')--9 April 2001
SELECT FORMAT(@d, 'd MMMM yyy', 'fr-FR')--9 avril 2001 
yyyy

The year as a four-digit number.

DECLARE @d DATETIME
SET @d='4/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'yyyy')--2001
SELECT FORMAT(@d, 'd-M-yyyy')--9-4-2001
SELECT FORMAT(@d, 'd MMMM yyyy')--9 April 2001
SELECT FORMAT(@d, 'd MMMM yyyy', 'fr-FR')--9 avril 2001 
yyyyy

The year as a five-digit number.

DECLARE @d DATETIME
SET @d='4/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'yyyyy')--02001
SELECT FORMAT(@d, 'd-M-yyyyy')--9-4-02001
SELECT FORMAT(@d, 'd MMMM yyyyy')--9 April 02001
SELECT FORMAT(@d, 'd MMMM yyyyy', 'fr-FR')--9 avril 02001 
--Era Format
g or gg

The period or era.

DECLARE @d DATETIME
SET @d='4/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'MM/dd/yyyy g')--04/09/2001 A.D.
SELECT FORMAT(@d, 'MM/dd/yyyy g', 'fr-FR')--04/09/2001 ap. J.-C.
SELECT FORMAT(@d, 'MM/dd/yyyy gg')--04/09/2001 A.D.
SELECT FORMAT(@d, 'MM/dd/yyyy gg', 'fr-FR')--04/09/2001 ap. J.-C. 
--Hour (Time Format)
h

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

DECLARE @d DATETIME
SET @d='5/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'h ')--4
SELECT FORMAT(@d, 'h:m:s')--4:54:8
SELECT FORMAT(@d, 'h:m:s', 'fr-FR')--4:54:8 
hh

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

DECLARE @d DATETIME
SET @d='5/9/2001 4:54:08 PM'
 
SELECT FORMAT(@d, 'hh')--04
SELECT FORMAT(@d, 'hh:m:s')--04:54:8
SELECT FORMAT(@d, 'hh:m:s', 'fr-FR')--04:54:8 
H

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

DECLARE @d DATETIME
SET @d='5/9/2001 6:54:08 AM'
 
SELECT FORMAT(@d, 'H ')--6
SELECT FORMAT(@d, 'H:m:s')--6:54:8
SELECT FORMAT(@d, 'H:m:s', 'fr-FR')--6:54:8 
HH

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

DECLARE @d DATETIME
SET @d='5/9/2001 6:54:08 AM'
 
SELECT FORMAT(@d, 'HH')--06
SELECT FORMAT(@d, 'HH:m:s')--06:54:8
SELECT FORMAT(@d, 'HH:m:s', 'fr-FR')--06:54:8 
--Min (Time Format)
m

The minute, from 0 through 59.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:08 AM'
 
SELECT FORMAT(@d, 'm ')--9
SELECT FORMAT(@d, 'mm')--09  
mm

The minute, from 00 through 59.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:08 AM'
 
SELECT FORMAT(@d, 'm ')--9
SELECT FORMAT(@d, 'mm')--09  
--Sec (Time Format)
s

The second, from 0 through 59.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:08 AM'
 
SELECT FORMAT(@d, 's ')--8
SELECT FORMAT(@d, 'ss')--08  
ss

The second, from 00 through 59.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:08 AM'
 
SELECT FORMAT(@d, 's ')--8
SELECT FORMAT(@d, 'ss')--08  
--Sec Fraction (Time Format)
f

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:f')--06:09:59:1 
ff

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:ff')--06:09:59:12 
fff

The milliseconds in a date and time value.

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:fff')--06:09:59:123 
ffff

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:ffff')--06:09:59:1234 
fffff

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:fffff')--06:09:59:12345 
ffffff

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:ffffff')--06:09:59:123456 
fffffff

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.123456789 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss:fffffff')--06:09:59:1234568 
F

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss f')--06:09:59 0
SELECT FORMAT(@d, 'hh:mm:ss F')--06:09:59 
FF

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss ff')--06:09:59 01
SELECT FORMAT(@d, 'hh:mm:ss FF')--06:09:59 01 
FFF

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss fff')--06:09:59 012
SELECT FORMAT(@d, 'hh:mm:ss FFF')--06:09:59 012 
FFFF

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss ffff')--06:09:59 0123
SELECT FORMAT(@d, 'hh:mm:ss FFFF')--06:09:59 0123 
FFFFF

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss fffff')--06:09:59 01234
SELECT FORMAT(@d, 'hh:mm:ss FFFFF')--06:09:59 01234 
FFFFFF

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss ffffff')--06:09:59 012345
SELECT FORMAT(@d, 'hh:mm:ss FFFFFF')--06:09:59 012345 
FFFFFFF

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

DECLARE @d DATETIME2
SET @d='5/9/2001 6:09:59.012345009 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss fffffff')--06:09:59 0123450
SELECT FORMAT(@d, 'hh:mm:ss FFFFFFF')--06:09:59 012345 
--Time Zone (Time Format)
K

Time zone information.

DECLARE @d DATETIMEOFFSET
SET @d='6/15/2009 1:45:30 AM -07:00'
 
SELECT FORMAT(@d, 'K ')--   -07:00  
--AM/PM designator (Time Format)
t

The first character of the AM/PM designator.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss t')--06:09:59 A 
tt

The AM/PM designator.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT(@d, 'hh:mm:ss tt')--06:09:59 AM 
--Offset, UTC (Time Format)
z

Hours offset from UTC, with no leading zeros.

DECLARE @d DATETIMEOFFSET
SET @d='6/15/2009 1:45:30 AM +05:30'
 
SELECT FORMAT(@d, 'z ') -- +5  
zz

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

DECLARE @d DATETIMEOFFSET
SET @d='6/15/2009 1:45:30 AM +5:30'
 
SELECT FORMAT(@d, 'zz') -- +05 
zzz

Hours and minutes offset from UTC.

DECLARE @d DATETIMEOFFSET
SET @d='6/15/2009 1:45:30 AM +5:30'
 
SELECT FORMAT(@d, 'zzz') -- +05:30 
--Date, Time Separator (Time Format)
:

The time separator.

SELECT FORMAT(GETDATE(), 'hh:mm:ss tt') -- 10:52:15 PM 
/

The date separator.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT ( @d, 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ); --2001/05/09 06:09:59 AM 
--String
"string"

Literal string delimiter.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT ( @d, '"Today is "yyyy/MM/dd', 'en-US' ); --Today is 2001/05/09
SELECT FORMAT ( @d, '"Mail sent on: "yyyy/MM/dd' ); --Mail sent on: 2001/05/09 
--Escape Character
\

The escape character.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT ( @d, 'h hour', 'en-US' ); --6 6our ,not as expectd
SELECT FORMAT ( @d, 'h \hour' ); --6 hour
SELECT FORMAT ( @d, 'MM \m' ); --05 m
SELECT FORMAT ( @d, 'MM m' ); --05 9, not as expected 
--Single Custom Format Specifier
%

Defines the following character as a custom format specifier. To use any of the custom date and time format specifiers as the only specifier in a format string (that is, to use the "d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" custom format specifier by itself), include a space before or after the specifier, or include a percent ("%") format specifier before the single custom date and time specifier.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT ( @d, '%h'); --6
SELECT FORMAT ( @d, 'h'); --NULL 
--Invalid Format
Any Other Character

The character is copied to the result string unchanged.

DECLARE @d DATETIME
SET @d='5/9/2001 6:09:59 AM'
 
SELECT FORMAT ( @d, ' invalid h'); -- invali9 6
SELECT FORMAT ( @d, 'Qui\t in h \hour'); --Quit in 6 hour 
  Note : Use any combination of custom format string to customize your date and time format requirements.

References

The reference links from MSDN are given below. 

Summary 

In this article I have explained SQL Server's FORMAT function with String and Numeric format specifier. If I have missed anything or need any change in definition then please let me know. I hope you have enjoyed this article and got some value addition to your knowledge.  

You might be interested in the below articles.  

I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles. Thanks for reading. 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shemeer NS
Software Developer (Senior)
India India
Technology Specialist | CodeProject MVP | Visual Studio Gallery Contributor | Author | Geek | Netizen | Husband | ChessPlayer

Most of my articles are listed on top 5 of the respective 'Best articles of the month' and some of my articles are published on ASP.NET WebSite's Article of the Day section.

Check my contributions in Visual Studio Gallery and Code Project

Technical Blog: http://www.shemeerns.com
Facebook: http://facebook.com/shemeernsblog
Twitter : http://twitter.com/shemeerns
Google+ : http://google.com/+Shemeernsblog
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 Pin
Mihai MOGA13-Aug-14 4:20
professionalMihai MOGA13-Aug-14 4:20 
Generalarticle: SQL Server's FORMAT() function Pin
Member 109105857-Jul-14 14:38
memberMember 109105857-Jul-14 14:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150428.2 | Last Updated 6 Jul 2014
Article Copyright 2014 by Shemeer NS
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid