Format Function in SQL Server 2012

Rakesh Kalluri
9y
8k
0
0

Blog

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]

result

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)]