Format() Function in SQL SERVER

Recently, I was working on an SQL query, and I wanted to format a date column value in a USA English format. For this, I tried using the Format() function. In this tutorial, I will explain how to use the format() function in SQL Server with a few real examples.

Table of Contents

What is the Format() Function in SQL Server?

SQL Server’s FORMAT() function accepts a value and a format pattern, formats the value as a string using the format pattern, and returns the result.

With this function, you can format date/time and numeric values in various ways. For example, this format function will be used if I want to see the system date and time. Also, we can use the format date dd/mm/yy.

Format() Function Syntax

Below is the syntax of the format() function in SQL Server.

FORMAT(value, format, culture)

Where,

Value is a numeric value, date/time value, or any data type converted into a string and formatted with the specified format pattern.
FORMAT() – The function formats the provided value based on the defined format pattern.
Culture – Here, Culture means the language and the country. For example, the culture of English from the USA is en-us. Similarly, French from France is fr-fr.

Format() Function in SQL SERVER Examples

I will now show you a few examples of how the format() function can be used in SQL Server.

Example-1: Format Function in SQL for Number

Look at the example to check how the format function works for number. Here, N indicates the number after the decimal, where the number of zeroes will be displayed. By default, it will give the output two zeroes after the decimal.

select FORMAT(500, 'N')

If I take N0 [Zero], the output will display without zeroes.

If I take N4, then the result will be four zeroes.

Example-2: Format() Function for Number

See the other example for splitting a number through the Format Function for Number in SQL Server. Here, the number is split based on the hash I gave.

select FORMAT (978254356789, '####-####-####') AS OUTPUT;

If I split the hash with three, the output will display with 3 splitting numbers.

Example-3: Format() Function in SQL Server for Date

If you want to format the date in SQL, see the date format for the dates below with the output. I have given three languages for the date function. Here, we will get the output in English, German, and simplified Chinese.


Declare @dt Date = '07/12/2023';
select FORMAT ( @dt, 'D', 'en-US') 'US English'
      ,FORMAT ( @dt, 'D', 'de-de') 'German'
      ,FORMAT ( @dt, 'D', 'zh-cn') 'Simplified Chinese';

Example-4: Format() Function for a date in SQL Server

Here, we will see how to see the current date format in SQL Server. I have to show the date in the organization per the client’s requirements. Below is the output.

Declare @dt Date = '06/29/2024';
select FORMAT ( @dt, 'd', 'en-US') 'US English'
      ,FORMAT ( @dt, 'd', 'de-de') 'German'
      ,FORMAT ( @dt, 'd', 'zh-cn') 'Simplified Chinese';

Example-5: Format() Function for Currency in SQL Server

If the user wants to check for currency, we can use the Format() function. Here, we will see the details of the currency format in the SQL Server.

Select 
 FORMAT (1, 'C', 'in-In') AS 'INDIA',
 FORMAT (1, 'C', 'us-US') AS 'USA',
 FORMAT (1, 'C', 'ch-ch') As 'CHINA';

Format Function in SQL SERVER: Date and Time

You can also format the date and time values using the FORMAT() function. However, you must know the format patterns below before formatting the data and time values.

Format Pattern	Meaning
dd	It is the day from 01 to 31.
hh	It is the hour from 01 to 12.
mm	It is the minute from 00 to 59.
ss	It is the second from 00 to 59.
yy	it is the part of the year with only two digits
yyyy	It is the year with four digits
MM	it is the part of the month from 01 to 12.
HH	It is the hour from 00 to 23.
tt	It represents the AM/PM of the time.

Example-6: Format() Function for Date Conversion

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’, in the query below.

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

Format Function in SQL SERVER Date Pattern

The output is formatted to 05-11-2023 based on the ‘MM-dd-yyyy’ pattern.

Example-7: Format() Function for System Date and Time

If you want to see your system’s date and time, use the syntax below.

SELECT SYSDATETIME();

Format Function in SQL SERVER System Date and Time

Example-8: Format() Function for System Date function

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

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

Format Function in SQL SERVER Time with PM and AM

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

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

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

Format Function in SQL SERVER 24 Hour Time Format

Now, it shows the time in a 24-hour format. You can also format the custom time. So, use the query below.

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

Format Function in SQL SERVER Custom Time

Conclusion

Using the FORMAT() function in SQL Server, you learned how to format a given value to a number, currency, or general number in this SQL Server tutorial. Additionally, you learned how to use the various format patterns to format the date and time value.