Format function is one the important functions in SQL Server 2012. This function is very useful to format data in a specified format.
Today, we will discuss each and every aspect of this function. Also we will discuss the need/importance of this function in SQL server.
By using this function, we do not need to do a lot of scripting to format the data.
Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :
FORMAT ( value, format [, culture ] )
Parameters :
@value : Data to be converted in other format. (Mandatory)
@format : To define a format in nvarchar datatype. (Mandatory)
@culture : To define a culture. By default, it picks up culture from session. (Optional)
Purpose :
This function converts value from one format to another format by using culture. If culture is not available it will pick up the culture from the session. It will return value either in nvarchar data type or NULL.
Let me explain this with simple examples.
Example-1 : FORMAT – DATE WITH DEFAULT CULTURE
DECLARE @Date AS datetime=Getdate() Select @Date AS [DATE] SELECT FORMAT (@Date, 'dd/MM/yyyy') as [Result (dd/MM/yyyy)] SELECT FORMAT (@Date, 'MM/dd/yyyy') as [Result (MM/dd/yyyy)] SELECT FORMAT (@Date, 'yyyy/MM/dd') as [Result (yyyy/MM/dd)]
Example-2 : FORMAT – TIME WITH DEFAULT CULTURE
DECLARE @TIME AS DATETIME=Getdate() Select @TIME AS [TIME] SELECT FORMAT (@TIME, 'h:mm:ss tt') as [Result (h:mm:ss tt)] SELECT FORMAT (@TIME, 'hh:mm:ss tt') as [Result (hh:mm:ss tt)] SELECT FORMAT (@TIME, 'h:mm:ss') as [Result (h:mm:ss)] SELECT FORMAT (@TIME, 'hh:mm:ss') as [Result (hh:mm:ss)]
Example-3 : FORMAT – NUMBER WITH DEFAULT CULTURE
DECLARE @NUMBER AS NUMERIC(18,4)=12345.789 Select @NUMBER as [NUMBER] SELECT FORMAT (@NUMBER, '####0.00') as [Result (####0.00)] SELECT FORMAT (@NUMBER, '####0.000') as [Result (####0.000)] SELECT FORMAT (@NUMBER, '##,##0.00') as [Result (##,##0.00)] SELECT FORMAT (@NUMBER, '#') as [Result (#)]
Example-4 : FORMAT – CURRENCY WITH DEFAULT CULTURE
DECLARE @CURRENCY MONEY = '24500.50'; SELECT FORMAT (@CURRENCY, 'C') AS [RESULT]
Example-5 : FORMAT – DATE & TIME WITH MULTIPLE CULTURE
DECLARE @Date AS datetime=Getdate() Select @Date as [DATE] SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS [English Culture] SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','de-DE') AS [German Culture] SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ja-JP') AS [Japanese Culture] SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ar-SA') AS [Arabic Culture] SELECT FORMAT ( @Date,'dddd, MMMM dd, yyyy hh:mm:ss tt','ur-PK') AS [Urdu Culture]
Example-6 : FORMAT – CURRENCY WITH MULTIPLE CULTURE
DECLARE @CURRENCY MONEY = '24500.50'; SELECT FORMAT (@CURRENCY, 'C','en-US' ) AS [English Culture] SELECT FORMAT (@CURRENCY, 'C','de-DE' ) AS [German Culture] SELECT FORMAT (@CURRENCY, 'C','ja-JP' ) AS [Japanese Culture] SELECT FORMAT (@CURRENCY, 'C','ar-SA' ) AS [Arabic Culture] SELECT FORMAT (@CURRENCY, 'C', 'ur-PK' ) AS [Urdu Culture]
Example-7 : FORMAT – VALUE BY USING LANGUAGE INSTEAD OF CULTURE
SET LANGUAGE 'ENGLISH' SELECT FORMAT (245000, 'C') AS [Currency] SELECT FORMAT (GETDATE(),'D') AS [DATE] SELECT FORMAT (24500.282, '#,###,###0.00') AS [Currency]
Reference : MSDN
SQL Server Portal 
[…] in SQL server 2005,2008 & 2012. In SQL Server 2012, it became very simple with the help of “format” […]
[…] ways to do it in the earlier versions of SQL Server. In SQL Server 2012, you can also do it using FORMAT function (Method 3). Given below are different methods to get day, month and year from date time […]
[…] do have its solution via DATENAME function. However today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in […]
[…] (STR, LTRIM) to convert float to varchar and to trim extra spaces. In this method we will use ONLY FORMAT function to convert float to […]
[…] do have its solution via DATENAME function. However, today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in […]
[…] month using CONCAT function (without any single quotes) and then get the Month Name using FORMAT function. Given below is the […]
[…] month using CONCAT function (without any single quotes) and then get the Month Name using FORMAT function. Given below is the […]
[…] 2 : In the second method, we will use only ONE function (FORMAT) to achieve […]
[…] using multiple function such as DATENAME and LEFT function functions. However, lets solve it using FORMAT function only. Let me create an example to explain […]
[…] 2 : In this solution, we will use a new function shipped with SQL Server 2012 namely Format. You just need to pass the values in this function and it will format values as a percentage […]
[…] : In this solution, I used IIF & Format function introduced in SQL Server 2012. Given below is the […]
[…] came across this error message in SQL Server 2012 using Format function, after that I did some research about this error message and came across that this error […]
[…] this article is that we can format the phone numbers in any format using only one function, i.e. Format (introduced in SQL Server […]
[…] I succeeded. Given below is the script to calculate the first and last day of the year using Format function (shipped with SQL Server […]
[…] I discovered that we can re-script this solution much more efficiently than I did earlier, using FORMAT & TRY_CONVERT (new functions shipped in SQL Server […]
[…] solution, we need to use one of the new formatting functions shipped with SQL Server 2012 namely FORMAT function. I usually recommend this method because it gives you variety for […]
[…] solution, we need to use one of the new formatting functions shipped with SQL Server 2012 namely FORMAT function. I usually recommend this method because it gives you variety for […]
[…] 2012 and above. In this script, I will use a newly shipped (in SQL Server 2012) function namely Format to reduce the line of script in order to convert yyyymm to Mon-yyyy. Given below is the […]
[…] messages shipped with SQL Server 2012. This error message is some how related to Culture & Format, a new string function shipped with SQL Server 2012 as […]