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


SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format1

Solarwinds

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

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format2

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;

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format3

-- 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;

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format4

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;

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format5

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;

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format7

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;

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format8

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;

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format9

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.

SQL SERVER - Denali - String Function - FORMAT() - A Quick Introduction format10

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)

Solarwinds
,
Previous Post
SQL SERVER 2012 – String Function CONCAT() – A Quick Introduction
Next Post
SQLAuthority News – Automation of Data Mining Using Integration Services

Related Posts

23 Comments. Leave new

  • Partha Pratim Dinda
    September 17, 2011 12:18 pm

    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

    Reply
  • 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

    Reply
  • Wow!

    Thats really a great update from Microsoft.

    Thanks a lot sir for sharing :)

    Reply
  • Your code for Month should be using upper-case M, not lower-case m. In three of those cases, you are showing the result of minutes, not months.

    Reply
  • 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

    Reply
  • nakulvachhrajani
    October 6, 2011 1:32 pm

    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.

    Reply
    • 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.

      Reply
      • Nakul Vachhrajani
        October 8, 2011 7:45 pm

        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.

  • Thanks pinal……. nice post… formatting date for country…

    Reply
  • great code here but i try this code in sql server it did not execute.

    Reply
  • Great stuff. Thank you so much for being an answer to the cry of many

    Reply
  • Thanks a lot. It will prove very helpful to us as we support 10 Locales.

    Reply
  • 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.

    Reply
  • Thank u for giving nice understanding ur’s material
    it is most helpfull, i saw all ur material…….

    Reply
  • 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.

    Reply
  • It is very nice material

    Reply
  • Harold Rizaldo
    March 25, 2014 8:36 am

    Can I use the FORMAT() Function in SQL Server 2008?

    Reply
    • No, Format() function is for SQL Server 2012 and newers versions.
      You could use Cast and Convert (Transact-SQL).

      Reply
  • Hi Sir,

    I have been following your posts from long time. We have a below requirement

    1. If the Market is “North America” then time zone should be according to “North America”

    2. If the Market is “Europe” then time zone should be according to “Europe”

    3. If the Market is “Asia Pacific” then time zone should be according to “Asia Pacific”

    We can do this by handling logic’s based on Market, which is bit complicated.

    Can we get this by using any other simpler approach.

    Please let me know. Thanks.

    Thanks,
    Rakesh

    Reply
  • You can use
    1. Save in UTC and during display use the timezone information.
    2. Use datetimeoffset datatype

    Reply
  • how can i format a sring? like i have a string like 123-45-67 and i want to show it to user as 00123-045-067?

    Reply
  • what is the use of g15 in format function? eg.
    Format(12345.99,’g15′)
    there are many other such g9,g13,etc.

    Reply

Leave a Reply

Menu