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 (http://blog.SQLAuthority.com)

25 thoughts on “SQL SERVER – Denali – String Function – FORMAT() – A Quick Introduction

  1. Thanks Pinal for sharing the format () It will really helpful to solve many critical issue easily.

    Please check the example2 the currency format() image .

    Thanks,
    Partha

    Like

  2. Since it is. Net based, does using it result in a performance penalty? In Sql2005 at least, calling a simple clr based string function was much slower than built-ins like REPLACE etc

    Like

  3. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  4. Hi Pinal,

    Could you please helip me in writing a SQL function which formats the input string as follows.

    Any letter with a period after it in the firstname needs to be treated as Middle Initials and woill needs to be formatted as: Firstname-M

    For ex: Somename A. –> Somename-A
    A. B. Somename –> Somename-A-B

    Thanks in advance,
    Sri

    Like

  5. I guess it’s time to be more careful now. Representation of data should *always* be handled by the UI. The database is a data store – as long as data is stored in one universal format across the database, there is nothing more that the DB should be doing.

    It’s easy to get carried away and put all formatting logic in T-SQL code, but it’s like opening a can of worms.

    Like

    • Hi Nakulvachhrajani,

      I am not sure if your replied to my query or just expressed your view on the article. I am not storing the fomatted data in the database but I would
      like to genarate the output column in a report as described above.

      I have a table called “persons” where firstname is one column, all I want is to genarate a report with the firstname as one column.
      The formatting should be as follows:
      when the firstname is like ” Somename A.” then the output should be ” Somename-A”
      when the firstname is like ” A. B. Somename” then the output should be “Somename-A-B”

      (Any letter with a period after it in the firstname needs to be treated as Middle Initials and will need to be formatted as: Firstname-M)

      Thanks in advance,
      Sri.

      Like

      • Hello, Sri!

        I expressed my opinion on the feature in general, and the comment was in no way directed towards you. I am sorry if it appeared that way.

        In your case, you plan to use formatting when generating a report – which to me is a representational layer, making it legal to undertake such formatting exercises.

        My only concern was, and is, that users might end up storing formatted data – which creates problems down the road.

        Like

  6. Pingback: SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Denali – String Function – CONCAT() – A Quick Introduction « SQL Server Journey with SQL Authority

  9. SELECT FORMAT (GETDATE(), N’dddd MMMM dd, yyyy’) can be replaced with
    SELECT FORMAT (CURRENT_TIMESTAMP, ‘D’)
    This is quicker/simpler to write, and can be used with the optional culture param.
    GETDATE() and CURRENT_TIMESTAMP return the same value – GETDATE() is T-SQL specific, and CURRENT_TIMESTAMP is standard SQL.

    Like

  10. how do I practice many exercises on SQL Server. I’m new to this. I know the basics but I could not able to learn deeply. If you are aware of any links of SQL exercises please let me know. My mail id is [removed]. Thank you.

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

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