In SQL Server, the FORMAT function is used to format date and number values according to specified styles or patterns. It provides a flexible way to control the display format of data.
Syntax
FORMAT(value, format_string [, culture])
- value: The date or number you want to format.
- format_string: A string that specifies the format to apply. This string can contain standard format strings or custom format strings.
- culture (optional): A string that specifies the culture to use for formatting. If not provided, the default language of the SQL Server instance will be used.
Formatting Dates
You can format date values by specifying format strings. Here are some common date format strings:
- yyyy: Four-digit year
- MM: Two-digit month (01-12)
- dd: Two-digit day of the month
- hh: Two-digit hour (01-12)
- HH: Two-digit hour (00-23)
- mm: Two-digit minute
- ss: Two-digit second
Example
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;
This will return the current date in the format "YYYY-MM-DD".
Formatting Numbers
When formatting numbers, you can use the following standard format strings:
- C: Currency format (e.g., $1,234.56)
- N: Number format with commas (e.g., 1,234.56)
- P: Percent format (e.g., 123.00%)
- E: Scientific notation (e.g., 1.23E+03)
Example
SELECT FORMAT(1234.56, 'N2') AS FormattedNumber;
This will return "1,234.56", formatted as a number with two decimal places.
Using Culture
You can specify the culture for formatting.
Example
SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy', 'en-US') AS LongDate,
FORMAT(1234.56, 'C', 'fr-FR') AS FormattedCurrency;
This will return the long date format in English (US) and format the number as currency in French format, which uses a comma as decimal separator.
Complete Example
SELECT
FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate,
FORMAT(1234.56, 'N2') AS FormattedNumber,
FORMAT(1234.56, 'C', 'en-US') AS FormattedCurrency,
FORMAT(1234.56, 'P', 'fr-FR') AS FormattedPercent;
Notes
- The FORMAT function is slow compared to other methods of formatting date and number types, especially in large datasets. If performance is an issue, consider using CONVERT and CAST functions for formatting.
- Be cautious about potential localization issues when specifying cultures, especially in regions where date formats and number formats differ significantly.
The FORMAT function is a powerful tool to ensure that your output data meets the specific formatting requirements desired for users or applications.