Format() function in SQL Server 2012


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;

format12) Display the date using Tamil Culture

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

format23) 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

format34) Display current date time in US format

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

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

Advertisements

Tagged: , , , , , , , , , , ,

2 thoughts on “Format() function in SQL Server 2012

  1. […] Format() function in SQL Server 2012 (appliedsql.wordpress.com) […]

    Like

  2. […] 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 …  […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: