How do you use the FORMAT function with custom format strings to format dates, times, and numbers in SQL Server?
SamPetr
Posted by SamPetr
Last Updated: July 12, 2024
FORMAT SQL
In SQL Server, the FORMAT function is used to format dates, times, and numbers according to specified custom format strings. This function provides flexibility in formatting output by allowing you to define the exact appearance of the data.
Syntax of the FORMAT function
The basic syntax of the FORMAT function is as follows: 
FORMAT(value, format_string [, culture])
- value: The value you want to format (e.g., a date, time, or numeric value). - format_string: A string that defines the format you want to apply to the value. - culture (optional): A string that specifies the culture in which the format is applied. If omitted, the current session's culture is used.
Formatting Dates and Times
You can use various format strings for formatting dates and times. Here are some common examples: 1. Standard Date and Time Format Strings (using 'd', 'M', 'y', etc.): 
SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy') AS FormattedDate  -- Outputs: "Monday, October 23, 2023"
2. Custom Date Formats: 
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS FormattedDateTime  -- Outputs: "2023-10-23 10:20:30"
3. Using Culture to Format: 
SELECT FORMAT(GETDATE(), 'd', 'en-US') AS USDate, 
          FORMAT(GETDATE(), 'd', 'fr-FR') AS FrenchDate
Formatting Numbers
The FORMAT function can also be used to format numeric values. Here are some examples: 1. Standard Numeric Format Strings: 
SELECT FORMAT(1234567.89, 'N0') AS FormattedNumber  -- Outputs: "1,234,568"
2. Custom Numeric Formats: 
SELECT FORMAT(1234567.89, 'C', 'en-US') AS CurrencyUS, 
          FORMAT(1234567.89, 'C', 'fr-FR') AS CurrencyFrance
Examples
1. Date Formatting: 
DECLARE @exampleDate DATETIME = '2023-10-23 10:20:30';
   SELECT 
       FORMAT(@exampleDate, 'yyyy-MM-dd') AS Formatted,
       FORMAT(@exampleDate, 'MM/dd/yyyy') AS USFormat,
       FORMAT(@exampleDate, 'dddd, MMMM dd, yyyy') AS LongFormat;
2. Time Formatting: 
DECLARE @exampleTime DATETIME = '2023-10-23 15:45:00';
   SELECT 
      FORMAT(@exampleTime, 'hh:mm tt') AS 12HourFormat,
      FORMAT(@exampleTime, 'HH:mm') AS 24HourFormat;
3. Number Formatting: 
DECLARE @exampleNumber DECIMAL(10, 2) = 12345.6789;
   SELECT 
       FORMAT(@exampleNumber, 'N2') AS NumberWithTwoDecimals,   -- "12,345.68"
       FORMAT(@exampleNumber, 'C', 'en-US') AS CurrencyUS,      -- "$12,345.68"
       FORMAT(@exampleNumber, 'P1') AS Percent;                  -- "1,234,567.89%"
Notes
- The FORMAT function is available in SQL Server 2012 and later. - Performance may be of concern in larger datasets, as FORMAT can be slower than other conversion functions such as CONVERT or CAST. - Always consider the locale when formatting to present data correctly according to the user's culture. In summary, the FORMAT function in SQL Server is a powerful tool for formatting dates, times, and numbers using custom format strings and cultures, enhancing the readability and presentation of data.
Default Image