SQLGeeksPro

Hi There! This is going to be a quick guide for SQL Server FORMAT Function. Formatting data in SQL Server often plays a crucial role in reporting, data presentation, and user interfaces.

Here we’ll walk you through few examples of the FORMAT() function in SQL Server.

Table of Contents

1. What is the FORMAT() Function?
2. Syntax of FORMAT()
3. Formatting Dates and Times
4. Formatting Numbers
5. Specifying Culture (Locale)
6. Conclusion

1. What is the FORMAT() Function?

Introduced in SQL Server 2012, the FORMAT() function is used to return a value formatted with the specified format and culture (locale).

It is particularly useful when you need to:

• Display results in a user-friendly format
• Output locale-specific formats
• Customize currency or date formats

2. Syntax of FORMAT()

It has a very simple Syntax

FORMAT( value , format [ , culture ] )

Now let’s understand the parameters:

Value: It is the value on which you want to apply the formatting. Not to mention, it should be one of the valid data types.

Format: Specify the format in which you require the output. (for example, "MM/DD/YYYY"). It does not support Composite formatting.  This parameter should contain a valid .NET format string in the NVARCHAR data type

Culture: It is an optional parameter. By default, SQL Server uses the current session language. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement.

Culture accepts any .NET Framework supported culture as an argument or else it raises an error.

3. Formatting Dates and Times

You can use FORMAT() to format date and time values using .NET date and time format strings.

Example 1: Formatting current Date in ‘yyyy-mm-dd’ or ‘dd-mm-yyyy’ format

SELECT GETDATE() AS DefaultDateFormat
GO
SELECT FORMAT(GETDATE(),'yyyy-MM-dd') AS FormattedDate
GO
SELECT FORMAT(GETDATE(),'dd-MM-yyyy') AS FormattedDate
GO

The result set is as follows:

In this case you can observe that the default date format of GETDATE() function is “yyyy-mm-dd hh:mm:ss.mmm” and we used the FORMAT() function to display it in “yyyy-mm-dd” and “dd-mm-yyyy” format.

Example 2: Display/Extract desired format from GETDATE()

SELECT FORMAT(GETDATE(),'d') AS ShortDate
GO
SELECT FORMAT(GETDATE(),'D') AS LongDate
GO
SELECT FORMAT(GETDATE(),'m') AS MonthDate
GO
SELECT FORMAT(GETDATE(),'g') AS [MM/DD/YYYY hh:mm AM/PM]
GO
SELECT FORMAT(GETDATE(),'G') AS [MM/DD/YYYY hh:mm:ss AM/PM]
GO

You can observe the differences in result set for each parameter:

4. Formatting Numbers:

You can apply numeric format strings for currency, percentages, and custom number formats

Example1: Formatting Numbers

SELECT FORMAT(1234.56, '#,##0.00') AS [Comma Separated Formatted Number];
GO
SELECT FORMAT(1234.56, '#,##0.000') AS [Comma Separated Formatted Number With Additional Decimal Place];
GO
SELECT FORMAT(123456789, '#,#') AS [Thousand Separator];
GO
SELECT FORMAT(123456789.566, '#,#.##') AS [Thousand Separator with rounded to a specified number of decimal places]
GO
SELECT FORMAT(123456789, '##-##-#####') AS [Dash Separator];
GO

Notice the differences in output. Column names are self explanatory.

Example 2: Formatting Currency

SELECT FORMAT(1.5, 'c', 'hi-in') AS [Indian Currency / Rupee]
GO
SELECT FORMAT(1.5, 'c', 'en-US') AS [US Currency / Dollar]
GO
SELECT FORMAT(1.5, 'c', 'en-gb') AS [Great Britain Currency / Pound]
GO
SELECT FORMAT(1.5, 'c', 'de-de') AS [German Currency / Euro]
GO
SELECT FORMAT(1.5, 'c', 'ru-ru') AS  [Russian Currency / Ruble]
GO
SELECT FORMAT(1.5, 'c', 'gl-es') AS  [Spain Currency / Euro]
GO

The result set with self explanatory column names:

Example 3: Percentage Formatting

SELECT FORMAT(0.756, 'P') AS [Percentage]
GO
SELECT FORMAT(0.756, 'P0') AS [WholeNumberPercentage]
GO
SELECT FORMAT(0.756, 'P3') AS [ThreeDecimalsPercentage]
GO
SELECT FORMAT(0.756, 'P4') AS [FourDecimalsPercentage]
GO
SELECT FORMAT((47.0/48.0), '#,##.0%') AS [Percentage Rounded to One Decimal Place]
GO
SELECT FORMAT((47.0/48.0), '#,##.##%') AS [Percentage Rounded to Two Decimal Places]
GO

Result set with self explanatory column names:

5. Specifying Culture (Locale)

Now let’s see formatting dates into different languages with the culture parameter.

SELECT FORMAT(GETDATE(), 'd','hi-in') AS [Indian (hi-in) (DD-MM-YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','en-US') AS [US English (en-US) (MM/DD/YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','en-gb') AS [Great Britain English (en-gb) (DD/MM/YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','de-de') AS [German (de-de) (DD.MM.YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','zh-cn') AS [Chinese (zh-cn) (YYYY/MM/DD)]
GO 
SELECT FORMAT(GETDATE(), 'd','ru-ru') AS [Russian (ru-ru) (DD.MM.YYYY)]
GO
SELECT FORMAT(GETDATE(), 'd','gl-es') AS [Spain (gl-es) (DD/MM/YYYY)]
GO

Please note the self explanatory column names for each:

Conclusion

The FORMAT() function is an incredibly flexible tool in SQL Server, ideal for making your output more user-friendly and internationalized. While it should be used with care in terms of performance, it greatly simplifies the formatting of numbers and dates compared to older methods.

Start using FORMAT() in your reporting queries to make your data presentation cleaner and more consistent.