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

September 17, 2011

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

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

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)

SQL Function, SQL Scripts

SQL SERVER 2012 – String Function CONCAT() – A Quick Introduction

SQLAuthority News – Automation of Data Mining Using Integration Services

SQL SERVER – Add Failover Cluster Node Fails With Error – This SQL Server Edition Does Not Support the Installed Number of Cluster Nodes

March 3, 2017

SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query

September 24, 2009

SQL SERVER – DISTINCT Keyword Usage and Common Discussion

December 20, 2007

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
Gill Bates
September 18, 2011 7:43 am
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
Raghav
September 21, 2011 10:26 pm
Wow!
Thats really a great update from Microsoft.
Thanks a lot sir for sharing :)
Reply
aaronbertrand
October 3, 2011 9:12 pm
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
Sri
October 5, 2011 6:49 pm
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
- Sri
  October 7, 2011 3:07 pm
  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.
Vijayakumar P
October 21, 2011 11:08 am
Thanks pinal……. nice post… formatting date for country…
Reply
waqas
December 28, 2011 6:20 pm
great code here but i try this code in sql server it did not execute.
Reply
Saint Sam
February 28, 2012 12:12 pm
Great stuff. Thank you so much for being an answer to the cry of many
Reply
Amit Sawant
July 16, 2012 9:28 am
Thanks a lot. It will prove very helpful to us as we support 10 Locales.
Reply
Malcolm
December 19, 2012 4:42 am
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
sudhakar reddy
December 23, 2012 11:12 am
Thank u for giving nice understanding ur’s material
it is most helpfull, i saw all ur material…….
Reply
Ritchie Roy
January 26, 2013 5:33 am
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
prasad
August 29, 2013 3:48 pm
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
- Hector
  November 20, 2014 2:18 am
  No, Format() function is for SQL Server 2012 and newers versions.
  You could use Cast and Convert (Transact-SQL).
  Reply
Rakesh
December 29, 2014 8:09 pm
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
blakhani
December 30, 2014 7:28 am
You can use
1. Save in UTC and during display use the timezone information.
2. Use datetimeoffset datatype
Reply
Mubeen
October 26, 2015 4:11 pm
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
Mitesh Shah
June 6, 2016 10:16 am
what is the use of g15 in format function? eg.
Format(12345.99,’g15′)
there are many other such g9,g13,etc.
Reply