Formatting Numeric Value, Currency And DateTime With Culture In SQL Server 2012 (“Code Named Denali”)

 

Format() is most awaiting function in SQL Server 2011 (“Denali”) most of the developers faces lots of problem in formatting datetime, numeric value and currency. But with the introduction of FORMAT() Function formatting has become more and more easy. FORMAT() function is simple and easy to use and a single function to format numeric, data time and currency format. The best part is FORMAT() function is culture aware.

Lets Explore It

To Generate Following CodeNumber. Series

00001,00002,00004….So On

We Use Replicate Function

Select REPLICATE('0',5-Len('1'))+'1' AS Data

Now With FORMAT() Function

Select FORMAT(1,'00000') As Data

Decimal Format

Showing different decimal format Is very easy with Format() function

Select 1 Id,'.0' as FormtCode, FORMAT(20.5678,'.0') As Data
Union All
Select 2 Id,'.00' as FormtCode,FORMAT(20.5678,'.00') As Data
Union All
Select 3 Id,'.000' as FormtCode,FORMAT(20.5678,'.000') As Data
Union All
Select 4 Id,'.0000' as FormtCode,FORMAT(20.5678,'.0000') As Data
Union All
Select 5 Id,'.00000' as FormtCode,FORMAT(20.5678,'.00000') As Data
Order By Id

Output :

 

Currency Format :

Select 'c0' as FormatCode, Format(177,'c0') as Format
Union All
Select 'c1' as FormatCode,Format(177,'c1') as Format
Union All
Select 'c2' as FormatCode,Format(177,'c2') as Format
Union All
Select 'c3' as FormatCode,Format(177,'c3') as Format

Output:

Currency Format With Culture :

Select 'en-us' as FormatCode, Format(1609,'c','en-us') as Format
Union All
Select 'en-in' as FormatCode,Format(1609,'c','en-in') as Format
Union All
Select 'hi-in' as FormatCode,Format(1609,'c','hi-in') as Format
Union All
Select 'en-gb' as FormatCode,Format(1609,'c','en-gb') as Format
Union All
Select 'ja-jp' as FormatCode,Format(1609,'c','ja-jp') as Format
Union All
Select 'de-de' as FormatCode,Format(1609,'c','de-de') as Format
Union All
Select 'fr-fr' as FormatCode,Format(1609,'c','it-it') as Format

Output:

 

Using Culture With Data Time

Select 1 Id,'English (en)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','en') AS Format
Union
Select 2 Id,'Franch (fr)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','fr') AS Format
Union
Select 3 Id,'Hindi (hi)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','hi') AS Format
Union
Select 4 Id,'Japenese (ja)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','ja') AS Format
Union
Select 5 Id,'Urdu (ur)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','ur') AS Format
Union
Select 6 Id,'Gujrati (gu) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','gu') AS Format
Union
Select 7 Id,'Bangla (bn) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','bn') AS Format
Union
Select 8 Id,'Malyalam (ml) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','ml') AS Format
Union 
Select 9 Id,'Kannada (kn) ' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','kn') AS Format
Union All
Select 10 Id,'Punjabi (pa)' As FormatCode,FORMAT(Getdate(),'dddd, dd MMMM yyy','pa') AS Format

Output :

So Format() Function is very helpful and provides easy access to different format without putting much effort and writing complex logic.