Format Function in SQL Server 2012

In this blog you will learn how to format function in SQL Server 2012.

This is the one of the format string function introduced in SQL SERVER. It returns the value to format in by specified format and culture.

It returns the Nvarchar Value type.

Syntax:

FORMAT (value, format [, culture])

Value – The value to be formatted.
Format – The specified format in which value will be formatted.
Culture – The Culture is optional. If we did not provide the culture SQL-SERVER,  default uses the Culture of the current session.

Comparing to the Format function with Convert:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]



Comparing to the format function, convert function is almost similar. We can also achieve whatever the output format function by using convert function, but a developer the always need to remember the format code 101. This is little bit difficult.

Performance wise Convert is little faster than the Format function. My recommendation is to use Convert function wherever possible. Format function is not a SQL SERVER NATIVE function, it is dependent on .NET CLR

Example for DATETIME format

SELECT FORMAT(GETDATE(),'d','en-US') AS[DATE IN US FORMAT]
SELECT FORMAT(GETDATE(),'d','en-IN') AS[DATE IN INDIA FORMAT]
SELECT FORMAT(GETDATE(),'dd/MM/yyyy')AS [dd/MM/yyyy]
SELECT FORMAT(GETDATE(),'MM/dd/yyyy')AS [MM/dd/yyyy]
SELECT FORMAT(GETDATE(),'yyyy/dd/MM')AS [yyyy/dd/MM]
SELECT FORMAT(GETDATE(),'t') AS [TIME FORMAT]
SELECT FORMAT(GETDATE(),'hh:mm:ss tt') [hh:mm:ss tt]

Example for CUURENCY format

SELECT FORMAT(100,'C','en-US')AS [CURRENCY IN US FORMAT]
SELECT FORMAT(100,'C','en-IN')AS [CURRENCY IN INDIA FORMAT]
SELECT FORMAT(100,'C0','en-IN') AS [CURRENCY IN INDIA FORMAT(WITH OUT DECIMALS)]
SELECT FORMAT(100,'C4','en-IN') AS [CURRENCY IN INDIA FORMAT(WITH 4 DECIMALS)]

Example for PERCENTAGE format

SELECT FORMAT(1,'P','en-US')AS [PERCENTAGE IN US FORMAT]
SELECT FORMAT(1,'P','en-IN') AS [PERCENTAGE IN INDIA FORMAT]
SELECT FORMAT(1,'P0','en-IN') AS [CUURENCY IN INDIA FORMAT(WITH OUT DECIMALS)]
SELECT FORMAT(1,'P4','en-IN') AS [CUURENCY IN INDIA FORMAT(WITH 4 DECIMALS)]

For more information about Formats.