SQL Server 2012 introduces this new function called FORMAT which returns a value in the specified format and also can optionally apply a regional format. This function relies on the .NET Framework.

In this article we will explore this function and see how it can be implemented.

FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where we pass on the date value or a numeric value. The second parameter is the .NET Framework format string. The format parameter is case sensitive. The third parameter is the culture. This can be any culture supported by the .NET Framework.

Let us now explore this function with some examples:
1) Display the date using Bengali Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Bn-IN' )
 AS DateInBangla;

2) Display the date using Tamil Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Ta-IN' ) AS
DateInTamil;

3) Display an amount into different currency structures

SELECT FORMAT(100, 'C', 'en-GB') AS Pounds,
FORMAT(100, 'C', 'en-US') AS Dollars,
FORMAT(100, 'C', 'es-ES') AS Euro,
FORMAT(100, 'C', 'en-IN') AS Rs;
GO

4) Display current date time in US format

SELECT FORMAT(GETDATE(), N'"Time now is "dddd MMMM dd, yyyy', 'en-US')
AS USTimeStamp;

For more details on this function please refer to this msdn link.

Related articles

• CONCAT() function in SQL Server 2012 (appliedsql.wordpress.com)

Tagged: .NET Framework, CLR, common language runtime, Database, format(), Functions, Microsoft, Microsoft SQL Server, Select (SQL), SQL, SQL Server 2012, T-Sql