SQL FORMAT() Function

The SQL FORMAT() function formats a number or date to a specific format and returns it as a string. It’s particularly useful for presenting numbers with thousands separators, currency symbols, or specifying decimal places, as well as for formatting dates into readable formats.

This function is supported primarily in SQL Server and MySQL, and it simplifies the customization of output for reports and applications.

The FORMAT() function’s behavior may vary slightly across SQL implementations, so it’s good to consult your database documentation for specifics.

In this tutorial, we will go through SQL FORMAT() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL FORMAT() Function

The basic syntax of the SQL FORMAT() function is:

</>
Copy
FORMAT(value, format [, culture]);

Each part of this syntax has a specific purpose:

  • value: The numeric or date value to be formatted.
  • format: The desired format pattern, such as '#,###.##' for numbers or 'yyyy-MM-dd' for dates.
  • culture (optional): The culture or locale code, such as 'en-US' or 'fr-FR', for localization of dates and numbers.

Examples: Using FORMAT() in SQL Queries

We’ll go through examples demonstrating the FORMAT() function in SQL, using sample numeric and date data.

1. Formatting a Number with Thousands Separator

To format the number 1234567.89 with a thousands separator:

</>
Copy
SELECT FORMAT(1234567.89, '#,###.##') AS formatted_number;

This query returns '1,234,567.89', displaying the number with commas as thousands separators.

SQL FORMAT() Function - Formatting a Number with Thousands Separator

2. Formatting a Number as Currency

To format a number as currency in US dollars:

</>
Copy
SELECT FORMAT(1234.56, 'C', 'en-US') AS formatted_currency;

This query returns '$1,234.56', with the dollar sign and thousands separator as per US currency format.

3. Formatting a Date with Custom Format

To format the date '2023-11-15' as 'November 15, 2023':

</>
Copy
SELECT FORMAT('2023-11-15', 'MMMM dd, yyyy', 'en-US') AS formatted_date;

This query returns 'November 15, 2023', formatting the date with the full month name, day, and year in US format.

4. Formatting Date for Different Cultures

To format the date '2023-11-15' in French culture (fr-FR):

</>
Copy
SELECT FORMAT('2023-11-15', 'dddd, dd MMMM yyyy', 'fr-FR') AS formatted_date;

This query returns 'mercredi, 15 novembre 2023', the date in French with the full weekday, day, month, and year.

FAQs for SQL FORMAT()

1. What does the SQL FORMAT() function do?

The FORMAT() function formats numbers and dates into specified patterns, adding separators or applying culture-specific formats.

2. Can FORMAT() handle NULL values?

Yes, if the value provided to FORMAT() is NULL, it returns NULL as the result.

3. What cultures are supported by FORMAT()?

FORMAT() supports standard culture codes like 'en-US', 'fr-FR', 'de-DE', etc., to customize date and number formatting.

4. How is FORMAT() different from CONVERT() in SQL?

FORMAT() is used for locale-specific formatting, whereas CONVERT() is typically used to convert data types.

5. Is FORMAT() supported in all SQL databases?

No, FORMAT() is supported primarily in SQL Server and MySQL. Other databases may have different functions for formatting.