SQL Server FORMAT() Function

 The FORMAT() function in SQL Server is used to format values according to a specified format and culture. It is particularly useful for formatting dates and numeric values in a customized way.

Syntax:

FORMAT(value, format, culture)

Parameters:

1. value: The value to format (e.g., a date or number).
2. format: A string specifying the format to apply. It follows .NET format patterns.
3. culture (optional): A string specifying the culture to use (e.g., 'en-US', 'fr-FR'). If omitted, the default language of the server is used.

---

Examples:

1. Formatting a Date

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;
-- Output: 2024-12-26

2. Formatting a Number

SELECT FORMAT(1234567.89, 'N') AS FormattedNumber;
-- Output: 1,234,567.89 (based on the default culture)

3. Using a Specific Culture

SELECT FORMAT(GETDATE(), 'dd MMMM yyyy', 'fr-FR') AS FormattedDate;
-- Output: 26 décembre 2024

4. Custom Currency Format

SELECT FORMAT(1234567.89, 'C', 'en-US') AS USCurrency;
-- Output: $1,234,567.89
SELECT FORMAT(1234567.89, 'C', 'fr-FR') AS FRCurrency;
-- Output: 1 234 567,89 €

---

Common Format Specifiers:

For Dates:

Specifier	Description	Example Output
d	Short date	12/26/2024
D	Long date	Thursday, December 26, 2024
t	Short time	10:15 AM
T	Long time	10:15:30 AM
yyyy	Year	2024
MM	Month (2 digits)	12
dd	Day (2 digits)	26

For Numbers:

Specifier	Description	Example Output
N	Number with commas	1,234,567.89
C	Currency	$1,234,567.89
P	Percentage	89.00 %
0	Zero placeholder	1234.50
#	Digit placeholder	1234.5

---

Notes:

• The FORMAT() function is available from SQL Server 2012 onwards.
• It is not the most performance-efficient method for formatting large datasets, as it involves .NET Framework processing.
• Use it primarily for displaying data rather than calculations.