SQL Server Format Function

The  SQL Format function is a SQL Server string function which is used to format the specified value in the given format such as to format the date/time values and number values.

It is used for locale-aware formatting of date/time and number values as strings

FORMAT function returns NULL, if the value specified in format is not valid.

The return type of FORMAT function is nvarchar or null

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 the culture argument is not provided then the language of the current session is used by SQL Server.

Lets look at an example of FORMAT In SQL Server.

FORMAT DATE USING CULTURES

Following statement uses Format function and returns a formatted date in different cultures.

DECLARE @date datetime = GETDATE();
SELECT
FORMAT(@date, ‘d’, ‘en-us’) ‘US’,
FORMAT(@date, ‘d’, ‘hi-in’) ‘India’,
FORMAT(@date, ‘d’, ‘de-DE’) ‘Germany’,
FORMAT(@date, ‘d’, ‘fr-FR’) ‘France‘,
FORMAT(@date, ‘d’, ‘zh-cn’) ‘China’

DECLARE @date datetime = GETDATE();
SELECT
FORMAT(@date, ‘D’, ‘en-us’) ‘US’,
FORMAT(@date, ‘D’, ‘hi-in’) ‘India’,
FORMAT(@date, ‘D’, ‘de-DE’) ‘Germany’,
FORMAT(@date, ‘D’, ‘fr-FR’) ‘France‘,
FORMAT(@date, ‘D’, ‘zh-cn’) ‘China’

 

FORMAT DATE USING CUSTOM FORMAT

Following Statement uses custom format to display date time.

DECLARE @date DATETIME = GETDATE() 
SELECT 'date in d/m/yy' as Particular, FORMAT(@date, 'd/m/yy') AS Format UNION
SELECT 'date in mm/dd/yyyy' ,FORMAT(@date, 'mm/dd/yyyy') UNION
SELECT 'date in dd/mm/yyyy', FORMAT(@date, 'dd/mm/yyyy') UNION
SELECT 'date in dd/mm/yy' , FORMAT(@date, 'dd/mm/yy') UNION
SELECT 'date in MMMM dddd yyyy hh:mm:ss:mmmm', 
FORMAT(@date, 'MMMM dddd yyyy hh:mm:ss:mmmm') UNION
SELECT 'date in MMMM yyyy, dddd hh:mm:ss:mmmm', 
FORMAT(@date, 'MMMM yyyy, dddd hh:mm:ss:mmmm')

FORMAT TO DISPLAY CURRENCY FOR CULTURES

Following statement uses format to display currency for different different cultures

DECLARE @amount money = 584020.89;
SELECT @amount as Amount ,
FORMAT(@amount, 'C', 'th-TH') 'Thailand',
FORMAT(@amount, 'C', 'de-DE') 'Germany',
FORMAT(@amount, 'C', 'en-gb') 'British',
FORMAT(@amount, 'C', 'en-us') 'US',
FORMAT(@amount, 'C', 'en-in') 'India',
FORMAT(@amount, 'C', 'fr-FR') 'France',
FORMAT(@amount, 'C', 'zh-cn') 'China'

 

 

Also Read

UPPER()

LOWER()

TRIM()

LRTIM()

RTIM()

SPACE()

TRANSLATE()

 2,498 total views,  1 views today