In this article, we shall learn Format Function of Sql Server 2012
Introduction
Sql Server 2012 has brought a lot of new functions for the T-Sql developers. In this article we will look into Format Function.
Purpose
It formats the value as indicated.This function is there in dot net for a long time and now has been added in Sql Server.
Syntax
Format (expression, format [, culture])
Where,
Expression = > the expression to format
Format => A valid dot net framework format pattern.
Culture => It is optional and is use for specifying the culture
Example 1: Date Formatting
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('fr')
Declare @dt Date = '06/15/2011'
Select
Culture
,Res1 = FORMAT(@dt,'d',Culture)
,Res2 = FORMAT(@dt,'yyyy/mm/dd',Culture)
From @t
/*
Culture Res1 Res2
------- ---- ----
en-US 6/15/2011 2011/00/15
fr 15/06/2011 2011/00/15
*/
Example 2: Currency Formatting
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('ru'),('no')
Declare @currency int = 200
Select
Culture
,FormattedCurrency = FORMAT(@currency,'c',Culture)
From @t
/*
Culture FormattedCurrency
------- -----------------
en-US $200.00
ru 200,00p.
no kr 200,00
*/
Even we can specify the number of characters that will appear after the decimal point. Let's have a look
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('ru'),('no')
Declare @currency money = 10.25
Select
Culture
,Res1 = FORMAT(@currency,'C1',Culture)
,Res2 = FORMAT(@currency,'C2',Culture)
,Res3 = FORMAT(@currency,'C3',Culture)
,Res4 = FORMAT(@currency,'C4',Culture)
From @t
/*
Culture Res1 Res2 Res3 Res4
------- ----- ----- ---- ----
en-US $10.3 $10.25 $10.250 $10.2500
ru 10,3p. 10,25p. 10,250p. 10,2500p.
no kr 10,3 kr 10,25 kr 10,250 kr 10,250
*/
Conclusion
SQL Server 2012 (Denali) seems to be very mature and promising and has embedded with many new functions.In this article we have looked into the Format function and it's usefulness into the T-Sql programming parlance.This function happens to be a good addition into the box and it supports advance formatting with specified culture which was a difficult choice with Cast and Convert.Hope the article will be useful.