Format Function in SQL SERVER

In this SQL Server tutorial, you will understand how to use format function in SQL Server for formatting the given value based on the different format specifiers.

You will learn how to format the numeric type value where you will understand how to format the value as a number, currency, etc.

Then you will learn how to format the given date and time value based on the specified date and time pattern. Also, you will learn about formatting the value based on the locale or language or the specific region.

Format Function in SQL SERVER

The FORMAT() function in SQL Server takes a value and format pattern, then formats that value as a string based on the provided format pattern.

This function is very useful because it allows you to format the date/time values, also the numeric values in different ways.

The syntax is given below.

FORMAT(value, format, culture)

Where,

• FORMAT(): It is the function that formats the provided value based on the defined format pattern.
• value: It is a numeric value, date/time value, or any kind of data type that is converted into a string and formatted with the specified format pattern.
• culture: It refers to the locale or language according to your region. Based on this language, the value is formatted, by default it uses the SQL Server’s current language.

Well, now you know about the syntax of the FORMAT() function. Let’s see how to format the given value on the basis of the different format patterns.

Format Function in SQL SERVER: Numeri Types

The FORMAT() function can format the numeric value with different formatting patterns. You can format the numeric values using the format pattern such as number, general, and currency format.

Suppose there is a Sales table with columns SaleID, ProductName, SaleDate, and SalesPrice. The table is shown below.

Look at the SalesPrice column that contains the sale price of each product and the price is with different decimal places.

Use the below query to format the SalesPrice column using the numeric format pattern.

SELECT SaleID, ProductName, SaleDate
	,FORMAT(SalesPrice,'N') AS FormattedNumeric
	FROM Sales;

In the above query part FORMAT(SalesPrice, ‘N’), formats each value within the SalesPrice column as a number using the ‘N’ which is the format pattern. Here ‘N’ means format the given value as a number.

After formatting the value, you can see that each value is formatted as a number with two decimal places. For example, the sale price of a Tablet is 299.55.

But also it formatted the value with a thousand separator for example Laptop Computer sale price is 1,000.00.

Next, let’s format the SalesPrice column value in a general format, For that use the below query.

SELECT SaleID, ProductName, SaleDate
	,FORMAT(SalesPrice,'G') AS FormattedGeneral
	FROM Sales;

Now here, you have used the format patterns as ‘G’ which represents the general format. If you look closely at the FormattedGeneral column in the above output, this time number is formatted without a thousand separator but with the 4 decimal places.

For example, look again at the sale price of Table product which is 299.5555. So When you format the value using the general format (‘G’), it doesn’t include the thousand separators whereas in the case of ‘N’ which is the number format, the thousand separator is included.

The last format pattern is ‘C’ which is currency format, if you want to format the number with a currency symbol use this pattern. In the SalesPrice column, each price is not formatted with a currency symbol and when you read that value it doesn’t make any sense.

So use the below query to format the SalesPrice column value with the currency symbol.

SELECT SaleID, ProductName, SaleDate
	,FORMAT(SalesPrice,'C') AS FormattedCurrency
	FROM Sales;

With the ‘C’ format pattern, you can see that each sale price in the FormattedCurrency column contains a dollar ($) symbol and also the thousand separator with two decimal places. For example, the Laptop Computer sale price is $1,000.00.

You can also change the currency symbol based on the country or locale for that you will need to use the third option of the FORMAT() function which is the culture.

For example, you have a number 234.55, and to format this number based on the USA currency use the culture as ‘en-US’. Run the below query.

SELECT FORMAT(234.55, 'C', 'en-Us');

The number 234.55 is formatted with a dollar currency symbol ($234.55). If you want to format the same number based on the Romansh-Switzerland currency symbol, use the ‘rm-CH’ culture. So execute the below query.

SELECT FORMAT(234.55, 'C', 'rm-CH');

This time the number 234.55 is formatted with the Romansh-Switzerland currency symbol (234.55 CHF). Based on the different locale you can format the number with a currency symbol.

Now you can compare the output of the above queries to see the difference when you use the ‘N’, ‘G’, and ‘C’ format pattern with the FORMAT() function.

Format Function in SQL SERVER: Date and Time

You can also format the date and time values using the FORMAT() function. But for formatting the data and time value, you need to know about the different format patterns which are shown below.

First, let’s format the date, for that use the below query.

SELECT FORMAT(CAST('2023-03-01' AS DATE),'dd/MM/yy');

In the above query, the FORMAT() function formats the ‘2023-03-01’ date based on the ‘dd/MM/yy’ pattern to ’01/03/23′.

You can use any format pattern such as ‘MM-dd-yyyy’ which is used in the below query.

SELECT FORMAT(CAST('2a023-05-11' AS DATE),'MM-dd-yyyy');

Look at the output, formatted to 05-11-2023 based on the ‘MM-dd-yyyy’ pattern.

Now, format the time value, and for that use the below query.

SELECT FORMAT(cast('10:30' as time), 'hh:mm');

SELECT FORMAT(cast('10:30' as time), 'hh.mm');

If you look at the time format patterns ‘hh: mm’ and ‘hh.m’, the separator dot (.) and the colon (:) are used. But for this separator, the FORMAT() function returns the NULL value as you can see in the above output.

To include these separators in your time value, you need to escape this kind of separator and for that, you will use the backslash ‘\’. So modify the time format pattern with backslash as shown in the below query.

SELECT FORMAT(cast('10:30' as time), 'hh\:mm');

SELECT FORMAT(cast('10:30' as time), 'hh\.mm');

After escaping the colon ‘:’ and dot ‘.’ using the backslash the FORMAT() function formats the time with specified format patterns such as ’10:30′ and ‘10.30’.

Now if you want to format the time with AM and PM, you can use the ‘tt’. But first, let’s check the current date and time of the system using the SYSDATETIME().

SELECT SYSDATETIME();

The SYSDATETIME() function returns the current time 13:05:42.9686474, to format this time with AM or PM using the below query.

SELECT FORMAT(SYSDATETIME(),'hh:mm:ss tt');

After adding the ‘tt’ format it shows the time 01:11:35 with PM as you can see in the above output. But here the time format is 12 hours, and you want the time format should be based on 24 hours.

For 24-hour format use the ‘HH’ pattern instead of ‘hh’, and run the below query.

SELECT FORMAT(SYSDATETIME(),'HH:mm:ss tt');

Now it shows the time based on the 24-hour format. Also, you can format the custom time. So use the below query.

SELECT FORMAT(CAST('01:36:23' AS DATETIME),'hh:mm:ss tt');

If you want to output the above time as 1 hour 36 minutes, then you can follow the format pattern which is specified in the below query.

SELECT FORMAT(CAST('01:36:23' AS DATETIME), 'hh \h\o\u\r\s mm \m\i\n\u\t\e\s') 
AS CustomFormattedTime;

Look how the time 1 hour 36 minutes is very readable. Here the format pattern hh and mm is the exact time value 1 and 36 of the specified time, and the string hours and minutes are based on the format pattern \h\o\u\r\s and \m\i\n\u\t\e\s respectively.

Now I hope that you have a proper understanding of using the FORMAT() function to format the values such as number, and date/time value based on the specified format pattern.

Conclusion

In this SQL Server tutorial, you learned how to format the given value to a number, currency, or general number using the FORMAT() function in SQL Server. Also learned about formatting the date and time value using the different format patterns.

You may also read:

• Format Number to 2 Decimal Places in SQL Server
• How to Format Number with Commas and Decimal in SQL Server?
• How to Convert Int to String with Commas in SQL Server?

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.