SQL Server String FORMAT() Function

SQL Server FORMAT() Function

The SQL Server FORMAT() function is used to format a date, time, number, or string value according to a specified format. This function is useful for displaying data in a readable and customizable format.

Syntax

SELECT FORMAT(value, format, culture);

The FORMAT() function takes the following arguments:

  • value: The value to be formatted.
  • format: The format pattern to apply to the value.
  • culture (optional): The culture code to use for formatting. If omitted, the default culture is used.

Example SQL Server FORMAT() Function Queries

Let's look at some examples of SQL Server FORMAT() function queries:

1. Basic DATE Formatting Example

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS formatted_date;

This query formats the current date as 'yyyy-MM-dd'. The result will be:

formatted_date
---------------
2024-06-03

2. Number Formatting Example

SELECT FORMAT(123456.789, 'N2') AS formatted_number;

This query formats the number 123456.789 to include two decimal places. The result will be:

formatted_number
-----------------
123,456.79

3. Currency Formatting Example

SELECT FORMAT(123456.789, 'C', 'en-US') AS formatted_currency;

This query formats the number 123456.789 as currency using the 'en-US' culture. The result will be:

formatted_currency
-------------------
$123,456.79

4. Custom String Formatting Example

SELECT FORMAT(1234, '0000') AS formatted_string;

This query formats the number 1234 as a string with leading zeros. The result will be:

formatted_string
----------------
1234

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the FORMAT() function.

Step 1: Creating a Table

This step involves creating a new table named sales_table to store some sample data.

CREATE TABLE sales_table (
    id INT PRIMARY KEY,
    sale_date DATETIME,
    amount DECIMAL(10, 2)
);

In this example, we create a table named sales_table with columns for id, sale_date, and amount.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the sales_table.

INSERT INTO sales_table (id, sale_date, amount) VALUES (1, '2024-01-01', 1234.56);
INSERT INTO sales_table (id, sale_date, amount) VALUES (2, '2024-02-01', 7890.12);
INSERT INTO sales_table (id, sale_date, amount) VALUES (3, '2024-03-01', 3456.78);

Here, we insert data into the sales_table.

Step 3: Using the FORMAT() Function

This step involves using the FORMAT() function to format the sale_date and amount columns.

SELECT id, FORMAT(sale_date, 'MMMM dd, yyyy') AS formatted_date, FORMAT(amount, 'C', 'en-US') AS formatted_amount
FROM sales_table;

This query retrieves the id, sale_date, and amount, and formats the sale_date as 'MMMM dd, yyyy' and the amount as currency using the 'en-US' culture for each row in the sales_table. The result will be:

id  formatted_date  formatted_amount
--- ---------------  -----------------
1   January 01, 2024 $1,234.56
2   February 01, 2024 $7,890.12
3   March 01, 2024    $3,456.78

Conclusion

The SQL Server FORMAT() function is a powerful tool for formatting dates, numbers, and strings according to specified patterns. Understanding how to use the FORMAT() function and its syntax is essential for effective data presentation and customization in SQL Server.