Database Simplified Headline Animator

Database Simplified

Sunday, 25 September 2011

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 :

image

 

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:

image

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:

image

 

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 :

image

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

No comments:

Post a comment