sql_func_format

SQL Formatting Functions

Formatting Functions Overview

SQL database systems provide functions to format how data, such as numbers, dates, and currencies, is displayed. The specific functions and their syntax vary significantly between different database platforms (e.g., SQL Server, MySQL, PostgreSQL, Oracle).

This chapter will primarily discuss the FORMAT() function as implemented in SQL Server (2012 and later) and then briefly mention alternatives in other systems.

The FORMAT() Function in SQL Server

In SQL Server (version 2012 and newer), the FORMAT() function is used to format a value (date, time, number) with a specified format string and an optional culture.

SQL Server FORMAT() Syntax:

SELECT FORMAT(value, format_string, [culture])
FROM table_name;

Parameter	Description
value	Required. The value to be formatted (can be date/time, numeric).
format_string	Required. A .NET Framework format string (standard or custom). For dates: ‘d’, ‘D’, ‘yyyy-MM-dd’. For numbers: ‘C’ (currency), ‘N’ (number), ‘P’ (percent).
culture	Optional. A string specifying the culture (e.g., ‘en-US’, ‘de-DE’). If omitted, the current session’s language is used.

Demo Database

We will use a sample “Products” table for our examples.

Below is a selection from the “Products” table:

ProductID
ProductName
SupplierID
CategoryID
UnitPrice
EntryDate


1
Chai
1
1
18.00
2023-01-15


2
Chang
1
1
19.50
2023-02-20


3
Aniseed Syrup
1
2
10.00
2023-03-10


4
Chef Anton's Cajun Seasoning
2
2
22.35
2023-04-05


5
Chef Anton's Gumbo Mix
2
2
25.00
2023-05-01

SQL Server FORMAT() Examples

1. Formatting a date:

The following SQL statement selects the product name and formats the EntryDate as ‘Month Day, Year’ using US English culture.

-- SQL Server Example
SELECT ProductName, FORMAT(EntryDate, 'D', 'en-US') AS FormattedEntryDate
FROM Products;

Result might look like: Chai | January 15, 2023

2. Formatting a number as currency:

The following SQL statement selects product name and formats UnitPrice as currency in US dollars.

-- SQL Server Example
SELECT ProductName, FORMAT(UnitPrice, 'C', 'en-US') AS FormattedPrice
FROM Products;

Result might look like: Chai | $18.00

3. Formatting the current date:

-- SQL Server Example
SELECT ProductName, UnitPrice, FORMAT(GETDATE(), 'yyyy-MM-dd') AS TodayFormatted
FROM Products;

This would add a column showing the current date formatted as YYYY-MM-DD for each product.

Formatting Functions in Other Database Systems

Other database systems have their own functions for formatting:

• MySQL:
  • `DATE_FORMAT(date, format_string)` for dates (e.g., `DATE_FORMAT(EntryDate, '%M %d, %Y')`).
  • `FORMAT(number, decimal_places)` for numbers (e.g., `FORMAT(UnitPrice, 2)`).
• PostgreSQL:
  • `TO_CHAR(value, format_string)` for both dates and numbers (e.g., `TO_CHAR(EntryDate, 'Month DD, YYYY')`, `TO_CHAR(UnitPrice, 'L9G999D99')` for locale-specific currency).
• Oracle:
  • `TO_CHAR(value, format_string, [nls_params])` for both dates and numbers (similar to PostgreSQL, e.g., `TO_CHAR(EntryDate, 'DD-MON-YYYY')`, `TO_CHAR(UnitPrice, 'L99G999D99')`).

Always refer to the specific documentation for your database system to understand the available formatting functions, format strings, and cultural settings.