SQL Server FORMAT() Function

Introduction

If you are a data scientist For analysts, data formatting in SQL is a fundamental skill to know. This helps in presenting data in a more readable and user-friendly manner, making it easier for stakeholders to understand. The SQL server FORMAT() function helps in customizing the appearance of dates, times, numbers, and currencies. In this article, we will explore the FORMAT function in detail and its various uses.

If you are just starting to explore SQL, here is a beginner's guide to help you: SQL for Data Science: A Beginner's Guide

General description

• Understand what the FORMAT() function is in SQL.
• Learn the syntax of the FORMAT() function.
• Learn how to format dates, times, numbers, and currencies using the FORMAT function in SQL.
• Explore some of the most common practical applications of the FORMAT function.

What is the Format function in SQL?

The SQL FORMAT() function helps convert different types of data into specific string formats. This data can be in the form of dates, times, numbers, or currency. It is especially useful when you need to present data in a particular format that suits your locale or user preferences.

Syntax

The basic syntax of the FORMAT() function is as follows:

FORMAT (value, format (, culture))

Here,

• worth: The value to format (can be a date, time, number, or currency).
• Format: A string defining the format to be applied to the value.
• culture (optional): A string specifying the culture in which the value is formatted.

Note that the format string in the FORMAT() function is case sensitive. For example, “MM” represents months, while “mm” represents minutes.

How to use the FORMAT() function in SQL

Let's see how we can use the FORMAT() function in SQL server to format dates, times, numbers, and currencies.

Date format

When dealing with dates, the FORMAT function allows you to display dates in a variety of formats.

Here are some examples:

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

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy') AS FormattedDate;

Suppose the date is July 4, 2024. The first example would format it as 2024-07-04while in the second example, it has the format Thursday, July 04, 2024.

Time format

The time format works in a similar way to the date format. Here are some examples:

SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS FormattedTime;

SELECT FORMAT(GETDATE(), 'hh:mm tt') AS FormattedTime;

Here, the first query formats the time in a 24-hour format, while the second uses a 12-hour format with AM/PM notation. So, if the time is 2:30 in the afternoon, the first format would display it as 14:30:15 while the second format would show it as 02:30 PM.

Format numbers

The FORMAT function is also very useful for formatting numbers. For example:

SELECT FORMAT(1234567.89, 'N') AS FormattedNumber;

SELECT FORMAT(1234567.89, '0.00') AS FormattedNumber;

In the first query, the number is formatted with commas as thousands separators (1,234,567.89). While in the second query, the number is formatted with two decimal places (1234567.89)

Currency format

Formatting monetary values ​​is another common use of the FORMAT function:

SELECT FORMAT(1234567.89, 'C', 'en-US') AS FormattedCurrency;

SELECT FORMAT(1234567.89, 'C', 'fr-FR') AS FormattedCurrency;

Here, the first query formats the number as US currency ($1,234,567.89), and the second formats it as French currency (1 234 567,89 €)

Practical applications

The FORMAT function is immensely useful in a variety of scenarios, such as:

• Generating reports: Customize the appearance of data in reports to match regional formats or user preferences.
• Data Export: Format data before exporting to ensure consistency and readability.
• User interfaces: Display data in applications in the appropriate format for a better user experience.

Conclusion

The SQL FORMAT function is a versatile and powerful tool for formatting data. Whether you're working with dates, times, numbers, or currencies, this function helps you present your data in a clear and culturally appropriate manner. Mastering the FORMAT function can improve the readability and professionalism of your data presentations. It also ensures that everyone you present your data to can read and understand it.

Learn more: SQL: A Complete Guide from Basics to Advanced Level

Frequent questions