Let's learn Format Function in Sql Server 2012

Niladri.Biswas
Posted by in Sql Server category on for Beginner level | Points: 100 | Views : 3710 red flag

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)