SQL SERVER – Denali – String Function – FORMAT() – A Quick Introduction

In SQL Server Denali, there are two new string functions being introduced, namely:

CONCAT()
FORMAT()

Today we will quickly take a look at the FORMAT() function. FORMAT converts the first argument to specified format and returns the string value. This function is locale-aware and it can return the formatting of the datetime and number to as per the locale specified string. This function also uses the server .NET Framework and CLR.

I was personally waiting for this function for long time and inclusion of this function made me very happy as this single function will solve lots of formatting issues for developer and leading them not to write bad code (sometime).

Now let us look at these examples showing how FORMAT() works:

Example 1: Location Aware FORMAT function with Date

DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS US_Result;
SELECT FORMAT ( @d, 'd', 'fr-FR' ) AS FR_Result;
SELECT FORMAT ( @d, 'd', 'de-DE' ) AS DE_Result;
GO

Example 2: Location Aware FORMAT function with Currency

DECLARE @d INT = 500;
SELECT FORMAT ( @d, 'c', 'en-US' ) AS US_Result;
SELECT FORMAT ( @d, 'c', 'fr-FR' ) AS FR_Result;
SELECT FORMAT ( @d, 'c', 'de-DE' ) AS DE_Result;
GO

Example 3: Various format of Datetime

-- Day
SELECT FORMAT ( GETDATE(), 'd', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'dd', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'ddd', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'dddd', 'en-US' ) AS US_Result;

-- Minutes
SELECT FORMAT ( GETDATE(), 'm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmm', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'mmmm', 'en-US' ) AS US_Result;

Please note, for months use ‘M’ (capital) and not the small ‘m’. In my example I used ‘m’ which displays the minutes. Thanks to Aaron Bertrand (blog|twitter) for pointing the error.

-- Year
SELECT FORMAT ( GETDATE(), 'y', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yy', 'en-US' ) AS US_Result;
SELECT FORMAT ( GETDATE(), 'yyy', 'en-US' ) AS US_Result;

Example 4: Various format of Currency

DECLARE @var INT = 50
SELECT FORMAT(@var,'c') AS Currency;
SELECT FORMAT(@var,'c1') AS Currency;
SELECT FORMAT(@var,'c2') AS Currency;
SELECT FORMAT(@var,'c3') AS Currency;

Example 5: Various miscalculation format

DECLARE @var INT = 50
SELECT FORMAT(@var,'p') AS Percentage;
SELECT FORMAT(@var,'e') AS Scientific;
SELECT FORMAT(@var,'x') AS Hexa;
SELECT FORMAT(@var,'x4') AS Hexa1;

Example 6: Format returning results in various language

SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'en-US') AS English_Result;
SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'hi') AS Hindi_Result;
SELECT FORMAT (GETDATE(), N'dddd MMMM dd, yyyy', 'gu') AS Gujarati_Result;

This all the functions can easily work the table column as well. Here is the quick example of the same.

Example 7: Format returning results based on table column

SELECT ModifiedDate,
FORMAT(ModifiedDate, N'dddd MMMM dd, yyyy','fr') Fr_ModifiedDate,
UnitPrice,
FORMAT(UnitPrice, 'c','fr') Fr_UnitPrice
FROM Sales.SalesOrderDetail

You can see that dates and price are now converted to French locale.

I think FORMAT is the most important function introduced in SQL Server Denali. It has made life easier for every developer.

Reference:  Pinal Dave (https://blog.sqlauthority.com)