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.